70
votes
Running Create Extension postgis gives ERROR could not open extension control file?
I just had the same problem on Ubuntu Server 14.04.
I installed the postgis extension from the official Ubuntu repositories using apt-get install postgis.
Then, find /usr -name postgis.control didn't ...
- 1,033
22
votes
Accepted
Using ST_Difference to remove overlapping features?
A self-join allows you to operate on the relationship between pairs of two features. But I don't think you're interested in pairs: for each feature, you want to operate on the relationship between ...
- 12.4k
22
votes
Cleaning geometries in PostGIS?
You can try ST_CollectionExtract to extract [Multi]Polygons from GeometryCollections. Use ST_Multi to force them as MuliPolygons.
UPDATE public.valid_lcmsouthshapefile
SET geom=ST_Multi(...
- 39.8k
21
votes
Running Create Extension postgis gives ERROR could not open extension control file?
In Ubuntu 14.04 you also need to install the postgresql-9.3-postgis-scripts package. After I ran
sudo apt-get install postgis postgresql-9.3-postgis-scripts
I was then able to successfully run
...
- 351
17
votes
Accepted
Cleaning geometries in PostGIS?
If you only want Polygons or Multipolygons from ST_MakeValid you can use ST_Dump to extract the constituent geometries and then test for the geometry type. ST_MakeValid will sometimes produce Points ...
- 13.2k
15
votes
Accepted
"libintl-9.dll is missing" when trying to run PostGIS 2.0 Shapefile and DBF Loader Exporter
It might be required in C:\Program Files\PostgreSQL\10\bin\postgisgui (or wherever the loader is installed).
It is however suspicious that the lib isn't included in the installer, and it might ...
- 34.6k
13
votes
Accepted
Distance in meters between two geographic coordinates
Your coordinates are out of order. If you reverse the order of coordinates in the first query, postgis says: 708.55982691.
In postgis it's lon, lat, not lat, lon.
- 3,051
12
votes
Accepted
Resolving relation does not exist in PostGIS?
This is a case sensitivity/quoting issue.
"PointsForGpxExport" and PointsForGpxExport are not the same table name.
PostgreSQL, per the SQL standard, case-folds unquoted identifiers. (It case-folds ...
- 774
12
votes
Accepted
How to work on downloaded copies of database tables in QGIS
QGIS comes with "offline editing" functionality in core. This downloads the data to a local spatialite database, so you can work on an offline copy and the delay caused by network roundtrips is gone. ...
- 26.2k
11
votes
Accepted
Is there a way to change ST_asGeoJSON return values?
Be aware that the GeoJSON specification states that
The GeoJSON object must have a member with the name "type". This member's value is a string that determines the type of the GeoJSON object.
http:...
- 26.2k
11
votes
Accepted
How to find points within a polygon in PostGIS
You should use the ST_within function and not the ST_intersect function. Here the documentation.
Here the sql code. You want only the points within greatermanchester
select a.*
from "Nov01" as a
join ...
- 1,002
10
votes
Accepted
PostGIS Geography Buffer producing different areas?
Internally, ST_Buffer(geography, ...) uses a fixed projection guess with _ST_BestSRID, which are typically UTM zones or whatever makes sense to the algorithm. This is why you see the differences, ...
- 39.8k
9
votes
Accepted
Ogr2ogr - shape to postgis - is there a way to use and preserve an existing primary key?
You can add the -lco FID=[your_id_column] switch to your OGR2OGR script.
Eg.
ogr2ogr -f "PostgreSQL" PG:"host=myhost user=myloginname dbname=mydbname
password=mypassword" mytabfile.shp -lco FID=id
...
- 146
8
votes
Publishing PostGIS Rasters in GeoServer?
I worked through several issues trying to use a postgis raster in geoserver similar to the OP. The below is how I successfully loaded my raster data.
Preconfiguration: A BNG geotiff layer, generated ...
- 414
8
votes
PostgreSQL : how do I make this sum
Assuming your data table look like:
streetid housenumber
100 12
100 15
101 12
101 18
You could do a basic SQL query:
SELECT streetid, count(*) FROM tablename ...
- 14.4k
8
votes
Is there any random data generator supporting geometry in PostGIS?
I made and tested the functions I need. For point generation I used a simplified version of the functions in trac.osgeo.org/postgis/wiki/UserWikiRandomPoint:
CREATE OR REPLACE FUNCTION random_point( ...
- 1,487
8
votes
"libintl-9.dll is missing" when trying to run PostGIS 2.0 Shapefile and DBF Loader Exporter
I found the libintl-9.dll file in the folder :
C:\Program Files\PostgreSQL\11\bin\
and copied it to the folder :
C:\Program Files\PostgreSQL\11\bin\postgisgui
then ran the .exe file in that folder ...
- 81
7
votes
Why is any pgr_* routing function taking forever based on OSM data in an pgrouting enabled DB
Use this guide to set up indexes for a spatial database. Here is the gist of it:
1. create indexes on ID, source and target columns.
2. create index using GIST on geom column.
3. vacuum
4. ...
- 3,520
7
votes
Accepted
How to render PostGIS raster with Mapnik XML?
For those who will stumble on this question like me..
It appears that the plugin has been generated (https://github.com/strk/mapnik/tree/2.3.x-pgraster) and has been merged in official Mapnik Repo.
...
- 168
7
votes
What is the precision of SELECT DISTINCT on PostGIS geometry column?
Given Paul Ramsey's excellent explanation of why the next question is what can be done about it. How do you SELECT DISTINCT on geometry fields and have it perform as expected?
In Paul's answer, I ...
- 339
7
votes
Running Create Extension postgis gives ERROR could not open extension control file?
For ubuntu 20.04, postgresql 13.1, run sudo apt install postgis postgresql-13-postgis-3-scripts. After which you can run create extension postgis; postgresql query.
- 181
7
votes
Accepted
Why is result of ST_Area or ST_Distance in SRID 4326 so small
All the measurements are always returned in the units of chosen CRS. WGS84 is defined in degrees of latitude or longitutde, thus it returns results in degrees.
If you want to obtain results in meters,...
- 4,142
7
votes
PostGIS: how to merge contiguous features sharing same attributes values?
You can do this with ST_ClusterIntersecting:
SELECT attr, unnest(ST_ClusterIntersecting(geom))
FROM lines
GROUP by attr;
- 12.4k
7
votes
Accepted
Create GeoJSON with CRS from Postgis
In PostgreSQL 9.4+, you can use the json_build_object and jsonb_build_object functions to construct JSON without subqueries, which feels more similar to how this is done outside of PostgreSQL. For ...
- 12.4k
6
votes
Why are date vaules displaying a trailing 'Z' when layers are published with Geoserver and a PostGIS data store?
It appears you were/are on the right track as the 'Z' is used for a timezone indicator, here is the source I found that best answers your question
"Z" is kind of a unique case for DateTimes. The ...
- 11.9k
6
votes
Python Geoserver gsconfig create resource from Postgis store table
Bit late to the party, but I'll answer anyway
1. Connect to GeoServer
cat = Catalog('http://localhost:8080/geoserver/rest')
2. Create a Workspace
ws = cat.create_workspace('newWorkspaceName','...
- 356
6
votes
Break line into 100m segments
The example in the PostGIS ST_LineSubstring doc is a bit dated. Modern Postgres has the JOIN LATERAL feature, which allows for more compact and efficient SQL. Here's a simple example:
WITH
data AS (...
- 3,716
6
votes
PostGIS: ERROR: lwpoly_from_lwlines: shell must have at least 4 points
Your query looks good. According to the source the error message means exactly what it says. One of your linestrings doesn't have at least 4 points.
Check your results with ST_NumPoints!
- 261
6
votes
Accepted
PostGIS strange difference in distance calc from different point defenitions
Your table seems to be an Amersfoort oblique stereographic (EPSG:28992). To use a lon/lat point as input, you need to create it as a lon/lat point, then transform it, so your distance function would ...
- 19.4k
6
votes
Accepted
How to get the number of polygons of a geometry in PostgreSQL
Use the geometry accessor ST_NumGeometries.
For example
SELECT ID, ST_NumGeometries(shape)
FROM myTable
If your geometries contain geometry types other than polygons, you will need to break them up ...
- 3,370
Only top scored, non community-wiki answers of a minimum length are eligible