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 ...
user avatar
  • 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 ...
user avatar
  • 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(...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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.
user avatar
  • 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 ...
user avatar
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. ...
user avatar
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:...
user avatar
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 ...
user avatar
  • 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, ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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( ...
user avatar
  • 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 ...
user avatar
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. ...
user avatar
  • 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. ...
user avatar
  • 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 ...
user avatar
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.
user avatar
  • 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,...
user avatar
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;
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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','...
user avatar
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 (...
user avatar
  • 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!
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 3,370

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