24
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.
20
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 ...
13
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 ...
10
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 ...
10
I can't speak to advantages/disadvantages vis-a-vis MySQL, but the PostGIS code is pretty widely regarded as one of the best (in terms of speed/functionality) and most mature (in terms of testing/real-world exposure) available.
By way of example, there was a talk at PGEast 2010 by some folks from the FAA on their converting their airport database (used by ...
9
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 ...
8
You need to install a specific EXTENSION in your database:
CREATE EXTENSION btree_gist ;
According to PostgreSQL documentation on btree_gist:
btree_gist provides GiST index operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, float8, numeric, timestamp with time zone, timestamp without time zone, time ...
7
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 ...
7
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 server ...
6
PostGIS supports it with ST_GeoHash
ST_GeoHash — Return a GeoHash representation (geohash.org) of the geometry.
6
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 must:
...
6
Assuming current Postgres 9.6, it can work.
Postgres can use indexes on a remote Postgres server. You may have to do more than for local tables, autovacuum does not run ANALYZE for remote tables automatically. Start by reading the chapter "Remote Query Optimization" in the manual.
Related answer with more details:
Postgres 9.5 foreign table inheritance ...
answered Jun 26 '17 at 15:49
Erwin Brandstetter
105k11 gold badges224 silver badges354 bronze badges
6
Just an update to Dennis' comment from the AWS thread, this is now possible.
Quote from the thread:
We just launched RDS PostgreSQL 10.5 which supports libprotobuf-c.
https://aws.amazon.com/about-aws/whats-new/2018/10/rds-postgresql-supports-minor-versions/
The official release notes (with emphasis added):
[...]
With this update, we have also ...
5
It seems that I had to re-run the AWS RDS PostGIS instructions:
CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser')
FROM (
SELECT nspname, relname
FROM pg_class c JOIN ...
5
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 ...
5
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
...
answered Jan 28 '16 at 10:00
Erwin Brandstetter
105k11 gold badges224 silver badges354 bronze badges
5
A couple of things that have caught you out here.
In the SQL Server version using the Point, the order of the coordinate is Lat Lon, eg Y X. The OGC Point construct is X Y or Lon Lat.
The next is that the PostGIS query you've posted actually returns 8.20039023523232e-005 which while appearing similar to the SQL Server result, is quite a lot smaller.
The ...
4
Speaking of some very major things. Here is a list of things PostGIS supports that are totally absent in MySQL and MariaDB.
SRID in calculations, give your points a different SRID and you'll get different values back. This is prop
Aggregate functions: to the best of my knowledge MySQL offers no spatial aggregates functions
K nearest neighbor: Only PostGIS ...
4
The column "row_num" doesn't exist because the logical order of processing requires the dbms to apply the WHERE clause before it evaluates the SELECT clause. The windowing function is part of the SELECT clause, so its alias isn't accessible in the same statement's WHERE clause.
The FROM clause is the very first part of the statement to be evaluated. That's ...
4
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 it ...
4
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 ...
answered Jul 19 '14 at 23:40
Erwin Brandstetter
105k11 gold badges224 silver badges354 bronze badges
4
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 ...
answered Aug 19 '14 at 16:21
Erwin Brandstetter
105k11 gold badges224 silver badges354 bronze badges
4
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 ...
4
There are a few problems here.
The id column in your schema is not declared as unique. What would you do if you had two of the same ids? @a_horse_with_no_name reads this as greatest-n-per-group, but why the greatest? What's the criteria for?
INSERT INTO telemetry ( id, lat, lon ) VALUES (1,1,1);
INSERT INTO telemetry ( id, lat, lon ) VALUES (1,1,2);
You'...
4
Background, functionality and performance
&& opperator
&& is bounding-box overlaps. All operators call functions in PostgreSQL: you can see this \doS+ && in this case && literally calls the PostGIS function geometry_overlaps. The only catch here is that && will make use of an index, from the docs
In general, you ...
4
I only need the distance to be accurate on a local scale and so curvature of the earth shouldn't be an issue for my application, but is there a performance benefit of either POINT or GIS in this context?
The native point is for geometry. If you want distance on Earth then those "points" are being projected onto a representation of Earth (sphereoid) and you ...
4
First, use EXPLAIN ANALYZE (not mere EXPLAIN) and show the result of \d on the table.
(psql). As a first point this,
ST_GeographyFromText('POINT(47.4667 8.3167)')
should be written as ST_MakePoint(47.4667, 8.3167)::geography
Your problem here is this pattern,
SELECT ST_Distance( ST_MakePoint(47.4667, 8.3167)::geography, geo_pt2) AS dist
...
ORDER BY ...
4
You might want to consider doing some Requirements Engineering for your project. When you have all the requirements listed, then you will want to write it up in a document and have companies like Oracle, Microsoft, IBM or others offer you a solution.
I don't think an open-source solution will work for the amount of data you are expecting without having ...
3
Seems you are running in a weakness of the query planner: The best index is sometimes not used for joining tables. Had a similar problem here:
Algorithm for finding the longest prefix (Chapter "Failed attempt with text_pattern_ops")
In Postgres 9.3 You could try this version with LEFT JOIN LATERAL:
SELECT *
FROM (
SELECT coord
FROM taduler....
3
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
