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 ...
- 13.6k
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 ...
- 182k
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 && ...
- 64.7k
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 ...
- 64.7k
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 ...
- 64.7k
7
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 ...
- 207
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
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
...
- 182k
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 ...
- 64.7k
6
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;
- 219
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)...
- 177
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 ...
- 64.7k
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(...
- 64.7k
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 ...
- 182k
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 ...
- 64.7k
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 ...
- 64.7k
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,
...
- 64.7k
4
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 ...
- 182k
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)
...
- 182k
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 ...
- 18.3k
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 ...
- 64.7k
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 ...
- 13.6k
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
---...
- 64.7k
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 ...
- 64.7k
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....
- 64.7k
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 ...
- 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 ...
- 64.7k
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 ...
- 41.3k
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,...
- 31.6k
Only top scored, non community-wiki answers of a minimum length are eligible