Skip to main content
11 votes
Accepted

PostgreSQL/PostGIS 9.6 broke my compound index

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 ...
joanolo's user avatar
  • 13.5k
11 votes
Accepted

Can PostGIS use spatial indexes on foreign tables?

Assuming at least Postgres 9.6 for local and remote server, and the same version of PostGIS installed, running this on the local server should do the trick: ALTER SERVER your_foreign_server_name ...
Erwin Brandstetter's user avatar
11 votes
Accepted

&& vs ST_Intersects performance

Background, functionality and performance && operator && is bounding-box overlaps. All operators call functions in PostgreSQL: you can see this \doS+ && in this case && ...
Evan Carroll's user avatar
  • 63.6k
8 votes

PostgreSQL vs. MySQL: spatial feature comparison

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 ...
Evan Carroll's user avatar
  • 63.6k
8 votes
Accepted

What's stored in spatial_ref_sys?

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 ...
Evan Carroll's user avatar
  • 63.6k
7 votes
Accepted

Support for libprotobuf-c on Amazon RDS

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-...
Dantooine's user avatar
6 votes

PostGIS vs. SQL Server for GIS data

Having worked with the GIS capabilities of both systems, I'd suggest the following: If you are primarily looking to store and query geometries, their capabilities are pretty comparable. If you want ...
N8allan's user avatar
  • 197
6 votes
Accepted

Dynamically define a RETURN table (column type, name) for subsequent loop

What you might need You should be able to avoid all the complication with a simple SELECT instead: SELECT t.*, ST_Intersection(x.geom, t.geom) AS geom2 FROM tbl t -- replace tbl with actual table ...
Erwin Brandstetter's user avatar
6 votes
Accepted

ERROR: invalid GeoJson representation

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 ...
Evan Carroll's user avatar
  • 63.6k
5 votes

PostgreSQL: Cannot change directory to /root

For me this did the trick, pay attention to quotes (') sudo -Hiu postgres 'pg_dump --column-inserts --data-only --table=someTable entities_db > /var/backups/anywhere/$(date +%Y%m%d_%H%M%S)...
Philippe Gachoud's user avatar
5 votes
Accepted

Diffrence in result from PostgreSQL (PostGIS) and SQL Server (Spatial)

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 ...
MickyT's user avatar
  • 1,111
5 votes
Accepted

For Postgres, efficiently get points within X distance of point Y?

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 ...
Evan Carroll's user avatar
  • 63.6k
5 votes
Accepted

PostgreSQL Geo Spatial Query is slow

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(...
Evan Carroll's user avatar
  • 63.6k
5 votes

Using parameters in stored procedure with plpgsql

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 ...
Erwin Brandstetter's user avatar
5 votes
Accepted

ERROR: could not open extension control file "/usr/share/postgresql/13/extension/postgis.control": No such file or directory

sudo apt install postgis postgresql-13-postgis-3-scripts create extension postgis;
Vy Do's user avatar
  • 209
4 votes
Accepted

postgis query id to multiline query

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 ...
Evan Carroll's user avatar
  • 63.6k
4 votes

Can I let PostgreSQL report the offending rows when a multi-row INSERT fails because of mismatched geometry types?

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 ...
Evan Carroll's user avatar
  • 63.6k
4 votes
Accepted

How can I modify an existing column into a foregin key in postgres?

First make sure you have no duplicate agency_names, or you may have problems. SELECT agency_name FROM agency GROUP BY agency_name HAVING count(*) > 1; Make sure your primary key is set on agency, ...
Evan Carroll's user avatar
  • 63.6k
4 votes

How can I manage an exception generated by Postgis?

You can wrap the PostGIS function ST_Contains() in a plpgsql (or other PL) function of your own and trap the exception there. Like: CREATE OR REPLACE FUNCTION f_ST_Contains(geometry, geometry) ...
Erwin Brandstetter's user avatar
4 votes
Accepted

Can PostGIS or Oracle Spatial and Graph scale to hundreds of terabytes of data, or should I use a NoSQL option like DynamoDB?

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 ...
John K. N.'s user avatar
3 votes

Best way to model a database structure to store multidimensional variables?

So you have some basic data. SELECT timestamp::timestamp, level, lat, long FROM ( VALUES (now(), 7, 1, 9) ) AS t(timestamp, level, lat, long); timestamp | level | lat | long ---...
Evan Carroll's user avatar
  • 63.6k
3 votes

Trigger to automatically calculate area when adding polygon in PostGIS

This is not a very good idea, and there is no need to do this. Just create a functional index. CREATE INDEX ON table ( ROUND( (ST_Area(geom)/1000)::numeric ,2 ) ); I would create the index on the ...
Evan Carroll's user avatar
  • 63.6k
3 votes

Can I let PostgreSQL report the offending rows when a multi-row INSERT fails because of mismatched geometry types?

Workaround (because I don't consider myself this to be "an answer") You can have one function loop through the result of the select you try to insert into the final table, and inform you before ...
joanolo's user avatar
  • 13.5k
3 votes
Accepted

Store both geometry and geography or store one and calculate the other?

To elaborate a little more, the database will be populated with pre-existing data that contains both British National Grid (EPSG:27700) (OSGR) and converted WGS84 (EPSG:4326) values, and there will be ...
Evan Carroll's user avatar
  • 63.6k
3 votes
Accepted

Finding the nearest geo points across two tables?

Don't use ST_Distance. It will never use an index. Instead use KNN distance with <->, and use ST_DWithin when possible. To find the nearest points from the table with 80M respect to 50M within 0....
Evan Carroll's user avatar
  • 63.6k
3 votes

GIS Point or discrete longitude and latitude columns?

Points are useful for spatial relationships. You can determine what points are within polygons (addresses within a city boundary, as a city is rarely a simple square), what point is closest to ...
indiri's user avatar
  • 2,211
3 votes
Accepted

GIS Point or discrete longitude and latitude columns?

A few advantages, Standardization It's standardized by the Open Geospatial Consortium (OGC) Typing Functions that use a Point are said to accept a Point. So it's advantageous for typing, and working ...
Evan Carroll's user avatar
  • 63.6k
3 votes

Is it possible to schema-qualify an operator?

Be aware that the OPERATOR construct (like OPERATOR(postgis.&&)) is not 100 % identical to the operator itself. Operators lose their implicit precedence and fall back to default operator ...
Erwin Brandstetter's user avatar
3 votes
Accepted

Can't get this PostgreSQL query to run faster

ST_DWithin ST_DistanceSphere(x,y)<t cannot use your spatial index. ST_DWithin can, depending on the selectivity. Instead use, ST_DWithin(u.geo_point, l.geo_point, 1000); Also, for testing ...
jjanes's user avatar
  • 40.1k
3 votes

How to delete constraint inside a trigger function

I think you want to set your foreign key DEFERRABLE INITIALLY DEFERRED so that you don't have to drop and recreate it every time there is an INSERT or other data change. Maybe even this is not needed,...
András Váczi's user avatar

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