18 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 ...
JGH's user avatar
  • 41.6k
15 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 ...
Michiel Stas's user avatar
14 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 ...
Mazu_R's user avatar
  • 1,102
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.
Rob Skelly's user avatar
  • 3,101
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. ...
Matthias Kuhn's user avatar
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, ...
Mike T's user avatar
  • 42k
9 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 ...
dbaston's user avatar
  • 13k
8 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 (...
dr_jts's user avatar
  • 4,968
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. ...
kttii's user avatar
  • 3,610
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.
Mitch's user avatar
  • 181
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;
dbaston's user avatar
  • 13k
7 votes

"libintl-9.dll is missing" when trying to run PostGIS 2.0 Shapefile and DBF Loader Exporter

Go to C:\Program Files\PostgreSQL\10\bin\postgisgui (or wherever the loader is installed). For example, for me, C:\Program Files\PostgreSQL\11\bin\postgisgui. Make a copy of the libintl-8.dll and ...
Jérémie Beghin's user avatar
6 votes

Running Create Extension postgis gives ERROR could not open extension control file?

I had the same issue, I just ran the command sudo apt-get install postgis I was using Ubuntu 18.04
Muhammad Fuzail Zubari's user avatar
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 ...
whyzar's user avatar
  • 12k
6 votes

Create GeoJSON with CRS from Postgis

You can create a feature collection by using row_to_json, selecting 'FeatureCollection' as type, and rolling up all you geometries into an array using array_to_json(array_agg(geom)) in a subquery, ...
John Powell's user avatar
  • 13.6k
5 votes

Add PostGIS spatial functions to a custom schema other than "public" in PostgreSQL

Here are example commands for installing the PostGIS extension into a gc schema: CREATE SCHEMA gc; CREATE EXTENSION postgis SCHEMA gc; Here are example commands to move PostGIS and its objects to a ...
Kenny Evitt's user avatar
5 votes
Accepted

Updating the Entry in geometry_columns for Spatial Table Created from a Query

With PostGIS 2.x, geometry_columns is a VIEW, not a TABLE. Try altering my_table.geom to your specifications using: ALTER TABLE my_table ALTER COLUMN geom TYPE geometry(LineString, 4326) USING ...
Mike T's user avatar
  • 42k
5 votes

Setting up pgRouting

You are trying to follow instructions from 2011. They don't apply to current versions of PostGIS/PgRouting. Instead, just do CREATE EXTENSION Postgis; CREATE EXTENSION pgrouting; PgRouting should ...
underdark's user avatar
  • 84k
5 votes
Accepted

How to get list of empty parcels (without building)

Cleanest way is with a left join where you aggregate on the buildings and then select parcels that have less then 1 building. You have to decide yourself whether ST_Intersects or ST_Contains fits your ...
tilt's user avatar
  • 3,267
5 votes

Change of geometry type and buffer

You are almost there with your query. The issue is that polygon is a native Postgres data type that is not used by PostGIS. Instead, you will want to use the geometry type, which can be refined to ...
JGH's user avatar
  • 41.6k
4 votes

What is the precision of SELECT DISTINCT on PostGIS geometry column?

Just an update, for PostGIS 2.4, SELECT DISTINCT works correctly for the points data in the OP: CREATE TEMP TABLE test (geom geometry); CREATE TABLE user=> INSERT INTO test user-> VALUES ...
tinlyx's user avatar
  • 11.1k
4 votes

Add PostGIS spatial functions to a custom schema other than "public" in PostgreSQL

here is your solution: CREATE SCHEMA gc; CREATE EXTENSION postgis SCHEMA gc; If you already installed the postGIS extension in your public schema and want to move in gc then write following code: ...
Tek Kshetri's user avatar
4 votes

Limit ST_DWithin() to first candidate found

To limit to one result, simply end the SQL with LIMIT 1 SELECT DISTINCT ON(g1.gid) g1.gid As gid, g2.gid As id_lines , round(cast(ST_DistanceSphere(g1.geom,g2.geom) as Numeric), 2) as distance ...
Liam G's user avatar
  • 2,156
4 votes
Accepted

Get ST_SummaryStats from Raster Table using geoJSON polygon

One thing I often do is check the formats available for a particular function, which you can do in psql using: \df+ ST_Clip which returns among other things, an argument data type list, one of which ...
John Powell's user avatar
  • 13.6k
4 votes
Accepted

Postgis 2 - ST_area : huge difference between 3857 and 4326

@ThingumaBob mentioned that it is the projection used in many popular web mapping applications (Google/Bing/OpenStreetMap/etc). Sometimes also known as EPSG:900913. EPSG 4326 uses a coordinate system ...
Maxima's user avatar
  • 1,701
4 votes
Accepted

ST_Centroid in view produces geometry without further definition from geometry(polygon, ssid)

Yes, this is possible but you can't do it after the view is created. Normally, the view would just inherit the geom constraints from the source table, but since you are changing the geom from polygon ...
pdavis's user avatar
  • 827
4 votes
Accepted

How to get number of Lane from OSM?

Looking at the .PBF file for Colorado, there are 2 line layers when importing into a QGIS project. The colorado-latest lines (NOT multistring) contains the HSTORE-type column "other_tags" In there, ...
DPSSpatial_BoycottingGISSE's user avatar
3 votes

Why are date vaules displaying a trailing 'Z' when layers are published with Geoserver and a PostGIS data store?

Start your Goserver with this Java option: -Dorg.geotools.localDateTimeHandling=true It will tell Geotools to not modify date objects (http://docs.geotools.org/latest/javadocs/org/geotools/factory/...
Kresinho's user avatar
3 votes

Prevent PostGIS Topology from splitting edges on line insert

PostGIS Topology is not good for modeling road networks. The ISO/IEC 13249-3 (SQL/MM) standard has a different API for that (Network Topology) which is not implemented in PostGIS yet. But, if it may ...
strk's user avatar
  • 86
3 votes

Running Create Extension postgis gives ERROR could not open extension control file?

Another source which caused the problem could be the missing PostGIS bundle, this can be defined in install menu or in your case afterwards. Therefor, open windows start, head to PostgreSQL an start ...
sqlmaster3000's user avatar

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