Hot answers tagged

24 votes
Accepted

PostGIS vs. SQL Server for GIS data

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 ...
  • 356
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 ...
9 votes
Accepted

Error in PostgreSQL/PostGIS Access to offline bands disabled

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 ...
  • 1,192
9 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 ...
  • 12.6k
8 votes
Accepted

PostgreSQL 9.3: pg_dump fails due to tiger.direction_lookup

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 ...
  • 10.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 ...
  • 57.2k
7 votes
Accepted

moving large PostgreSQL/PostGIS database

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 ...
  • 3,351
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-...
6 votes
Accepted

Adding standard set of constraints (rules) to PostGIS `raster` type column

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 ...
  • 52.8k
6 votes
Accepted

Why does this PostGIS query take so long to run?

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 ...
6 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 ...
  • 57.2k
6 votes
Accepted

&& vs ST_Intersects performance

Background, functionality and performance && opperator && is bounding-box overlaps. All operators call functions in PostgreSQL: you can see this \doS+ && in this case &&...
  • 57.2k
5 votes
Accepted

PostgreSQL, PostGIS: Copying a schema with a geometry column

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. ...
  • 314
5 votes
Accepted

Creating a unique constraint on a PostGIS 'raster' type column

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 ...
  • 52.8k
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 ...
  • 1,101
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 ...
  • 57.2k
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(...
  • 57.2k
5 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 ...
  • 57.2k
4 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 ...
  • 147
4 votes

Creating a unique constraint on a PostGIS 'raster' type column

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 ...
4 votes
Accepted

Very large btree index with few rows (openstreetmap gis data)

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 ...
4 votes

PostGIS radius query

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....
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 ...
  • 57.2k
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 ...
  • 57.2k
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, ...
  • 57.2k
4 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 ...
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 ...
  • 15.4k
4 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 ...
3 votes
Accepted

Problems installing Postgis extension on Ubuntu 14.04

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 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)...

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