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 ...
- 156k
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-...
- 86
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 ...
- 156k
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 ...
- 156k
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 ...
- 156k
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....
- 156k
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
...
- 156k
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 ...
- 156k
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.
- 168
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)...
- 157
Only top scored, non community-wiki answers of a minimum length are eligible