Hot answers tagged

11

This is one of those "It depends" questions. Performance depends on resources, contention, configuration, and the VM engine Uncontended VM host: If you properly resource a VM with uncontended high performance locally-attached or SAN storage, low contention for CPU resources, no memory overcommit or contention, fast dedicated network access, etc, it'll ...


7

First, use ST_SetSRID(ST_MakePoint(c.longitude, c.latitude),4326)::geography instead of ST_GeographyFromText('SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')') Per documentation: ST_MakePoint while not being OGC compliant is generally faster and more precise than ST_GeomFromText and ST_PointFromText. It is also easier to use if you ...


6

Try this: Re: could not change directory to "/root": Apparently you did "su postgres" from the root account, so you're still in root's home directory. It'd be better to do "su - postgres" to ensure you've acquired all of the postgres account's environment. Reading "man su" might help you out here.


6

Answer to question SELECT DISTINCT ON (name, zonedistrict_id) ST_Union(geom) as geom, gid, name, zonedistrict_id, zonestyle_id, longname FROM zones GROUP BY gid, name, zonedistrict_id, zonestyle_id, longname ORDER BY name, zonedistrict_id, zonestyle_id; It depends on what you are actually trying to achieve and what version of Postgres you are ...


5

I've worked with both Postgres and SQL Server. I found Postgres to be superior in GIS functionality. And while I'm going to briefly detail my findings below, I'd suggest this: Give yourself a brief but reasonable time period to review the unfamiliar solution over the one you know, with specific goals in mind. For example, maybe a 2 week time period to ...


4

A unique constraint creates a unique index to implement the constraint. The only index type in PostgreSQL that supports unique indexes is the default b-tree index type; you can't make a unique GIN or GiST index, e.g.: regress=> create unique index indexname on test USING GiST(id) ; ERROR: access method "gist" does not support unique indexes Data types ...


4

OK, so you: Dropped a bunch of constraints Did some work Attempted to add a completely unrelated random constraint and that failed You only showed two of the constraints in the output (why?) but the two you showed were CHECK constraints, not UNIQUE constraints. So it makes absolutely no sense to attempt to replace them with a UNIQUE constraint. You ...


3

As Craig explained very well, you cannot create a unique index on the type raster without all the necessary operators. Your second best bet to enforce uniqueness is to create a functional index on the text representation: CREATE UNIQUE INDEX us_tmin_enforce_scalex_rast ON chp05.us_tmin (cast(rast as text)) Note that this cannot be implemented as ...


3

As a part of my work I maintain a fairly large PostgreSQL database (around 120gb on disk, several multi-million-row tables) and have collected a few tricks on how to speed up the queries. First some comments on your assumptions: Yes, order is important, but it's only the first one that is really different, the rest are second class indexes. I'm not sure ...


3

Try this untangled version to update all rows of char_check: UPDATE car_check c SET fencing = ST_Contains(u.geom, ST_MakePoint(t.lat::float8, t.lng::float8)) FROM test t ,rule r JOIN ( SELECT DISTINCT ON (polyname) polyname, geom FROM unknown_table ORDER BY polyname, version DESC ) u USING (polyname) WHERE t.id = ...


3

UPDATE mytbl SET col2=myfn(col1) should do the trick


3

Start by using a current PostgreSQL version, preferably a PGDG build. See http://yum.postgresql.org. Then compare with the same hardware. There is no known significant performance difference with 64-bit vs 32-bit PostgreSQL on non-Windows platforms. Personally, I suspect that the 64-bit CentOS might have transparent huge pages enabled, which does cause ...


3

Off the top of my head, here are a few things to do, in no particular order. VACUUM FREEZE ANALYZE Add the indexes that you think are going to help. Execute as many queries as you can. Look at pg_stat_user_indexes to see which queries are being used. Look at the EXPLAIN ANALYZE plan for each query. Review ...


3

Adam, First that is old document -- new one is here: http://postgis.net/docs/AddGeometryColumn.html Though what they say in both is the same. The using that function is only need for 1.5 and below. 2.0 we encourage you to just create the table normally. something like CREATE TABLE test(gid serial primary key, geom geometry(POLYGON, 4326) ); pg_dump ...


3

You seem to expect that rows with NULL values are excluded from a B-tree index automatically, but that's not the case. Those are indexed as well and can be searched for. However, since: access_type ... is null in 90% of cases that's hardly useful in your case. Such common values hardly ever make sense in an index to begin with, be it NULL or any other ...


3

According to the README included in the dmg file, postgres binaries are installed in '/usr/local/pgsql/bin/' You should add these lines in your ~/.bashrc : export PSQL_DIR=/usr/local/pgsql/bin/ export PATH="$PSQL_DIR:$PATH" Update your environment with 'source ~/.bashrc' or run a new shell and psql should be available.


3

Best use ST_DWithin() in a LATERAL join: SELECT t.*, neighbor.* -- or only selected columns FROM tbl t LEFT JOIN LATERAL ( SELECT * FROM tbl t1 WHERE ST_DWithin(t.geo_point, t1.geo_point, 5000 * 1.609344) ) neighbor ON true WHERE t.address = 'my_address'; -- to restrict to a particular address 1.609344 being the factor for converting ...


3

Your query is needlessly complicated and can be untangled to this 100 % equivalent one: SELECT gid, id, ST_MakeValid(geom) AS geom FROM schema.table_polygons WHERE geom IS NOT NULL; Neither original nor this one remove any duplicates (except by possibly removing some with geom IS NULL, but I doubt that was your intention). Possible misunderstanding ...


2

We had the same problem with our RDS instances for a stretch of a week, but AWS support patched it on Sept 16. Now all RDS instances we launch with postgres 9.4.4 have functional and normal-logging postgis 2.1.5 on them. Your instances should be working as expected now too.


2

Explain We would have to see table definition, cardinalities and the EXPLAIN output to be certain, but the reason is most likely this: Only your spatial GiST index on a.geom can be used. The btree index is not applicable. Postgres walks through the "closest" rows until it finds the first two matching your predicate. Normally, more restrictive conditions ...


2

The condition: AND A.name = B.name only allows identical names, while you obviously want to match names with the same soundex value. So make that: AND soundex(A.name) = soundex(B.name) If gid is the primary key, you can also simplify to: GROUP BY a.gid, b.gid However, since there obviously is no aggregation at all, you can simply remove the ...


2

I would recommend dumping the 9.1 database from your new 9.3 server like this: pg_dump -h remoteserver -U remoteuser remotedbname -Fc -f my_old_server_backup.dump I recommend using the 9.3 pg_dump as pg_dump is always backwards compatible, but not forward compatible. In other words, the newer pg_dump will take care of any syntax changes that the new ...


2

Have you set the environment variables to enable rasters ? As of PostGIS 2.1.3, out-of-db rasters and all raster drivers are disabled by default. In order to re-enable these, you need to set the following environment variables: POSTGIS_GDAL_ENABLED_DRIVERS and POSTGIS_ENABLE_OUTDB_RASTERS in the server environment. If you want to enable offline ...


2

It seems to me that which db is better is not your primary concern here and instead you have two different considerations which cut against eachother, namely business knowledge vs customer desires. Ultimately that will be a business decision, not a technical decision. Obviously there is opportunity cost as Max has noted in a comment. There is no way ...


2

His response was that it was going to use more resources if it was array instead of text and that I should focus on my programming instead of wasting resources. Evidence? If he's arguing performance against what'd otherwise be a clarity and correctness improvement, the onus is on him to provide evidence. Delimited text is usually the wrong way to do ...


2

In general I have doubts about database servers on a VM. I don't doubt it is possible as per the previous answer but it strikes me as a complexity loss particularly for PostgreSQL where you could instead run several clusters on the same system without virtualization as long as you different IP addresses or ports. The thing people talk about on the pro side ...


2

First try connecting to the database as the postgres user jay@machine$ sudo su postgres postgres@machine$ psql Then create a new user, and why not create a database if you haven't already postgres=# CREATE USER jay WITH password 'donttell'; postgres=# CREATE DATABASE gis; postgres=# \q You should now be able to connect as yourself jay@machine$ psql ...


2

Just don't leave the Database field empty in the connection dialog. When left empty, it will attempt to connect to a database named after your OS login name, which probably doesn't exist in your case. The Host field should be left empty to use a Unix domain socket connection, as hinted by the snippet of your pg_hba.conf shown in the question. When you put ...


2

The point of reference comes from the cafe in the center, so you can use a subquery to retrieve it from the addresses table instead of the manual input: SELECT c.*, a.*, ST_Distance(t.lonlat, a.lonlat) AS distance -- pick columns you need FROM addresses a JOIN cafes c ON c.id = a.cafe_id , (SELECT lonlat FROM addresses WHERE ...


2

But that is 9.1. - Is pgadmin connected to the wrong postgresql? Yes. It appears that you're connected to your 9.1 server, suggesting that 9.1 is still running. Do pg_lsclusters to see what PostgreSQL installs you have and their status. Each runs on a different port. If you want to connect to 9.3 you need to check what port it runs on and connect ...



Only top voted, non community-wiki answers of a minimum length are eligible