23
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 ...
12
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 ...
9
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 ...
9
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 ...
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 with ...
8
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
138k17 gold badges327 silver badges474 bronze badges
7
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 ...
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 ...
7
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 ...
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
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 ...
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
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 ...
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
There ...
answered Jan 28 '16 at 10:00
Erwin Brandstetter
138k17 gold badges327 silver badges474 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 ...
5
No, it comes with PostGIS as a standardized catalog of the spatial reference systems your database can work with. It will be recreated when you CREATE EXTENSION postgis;. In fact, copying the table may cause the installation of PostGIS to fail. PostGIS is supposed to provide that table and the schema. You can see the table documented here.
That said, if you ...
5
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 ...
5
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 ...
5
From the docs on ST_GeomFromGeoJSON
ST_GeomFromGeoJSON works only for JSON Geometry fragments. It throws an error if you try to use it on a whole JSON document.
This means you'll want to cut out just the value from the geometry in your GeoJSON document,
SELECT ST_AsText(geom)
FROM ST_GeomFromGeoJSON($${ ...
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
138k17 gold badges327 silver badges474 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
138k17 gold badges327 silver badges474 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
The problem here is that the types aren't mismatched. PostGIS provides very few PostgreSQL types, namely:
box2d — A box composed of x min, ymin, xmax, ymax. Often used to return the 2d enclosing box of a geometry.
box3d — A box composed of x min, ymin, zmin, xmax, ymax, zmax. Often used to return the 3d extent of a geometry or collection of geometries.
...
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 ...
4
Problems:
You cannot parameterize identifiers (like table names) in plain SQL. You need dynamic SQL using EXECUTE for this.
amount is integer, but count() returns bigint.
RETURN amount AS id; is invalid syntax. To force a particular column name for a returned scalar (not a set-returning function), use an OUT parameter. Example:
Get latest timestamp from ...
3
As we can see here all the required packages are rightly updated in the official repository.
To solve the problem you can find a good start point here.
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
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 https://wiki.postgresql.org/wiki/...
Only top voted, non community-wiki answers of a minimum length are eligible