PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.

learn more… | top users | synonyms

1
vote
1answer
44 views

Appending to a cartodb table - duplicate key value violates unique key constraint

I'm trying to insert rows from one cartodb table into another using the method outlined here: INSERT INTO gfw_mining_test (the_geom, the_geom_webmercator, objectid, country,type, gfwid, st_area_sh, ...
1
vote
0answers
57 views

Cursor help on large spatial query in PostgreSQL

I have two very large tables on which I want to perform a spatial query. UPDATE table_a SET att_1 = b.att_1, att_2 = b.att_2 FROM table_a AS a JOIN table_b AS b ON ...
7
votes
2answers
174 views

Drawing line between points at specific distance in PostGIS?

I have a data of points along the streets, I would like to turn those dots into simple coloured lines. Any pointers what this problem may be called or any algorithms that can help me solve this? I ...
1
vote
0answers
35 views

Optimize Postgis contour lines DB

I'd like to try to optimize a world-wide 10m contour lines (isohypses) Postgis database(from SRTM). The complete DB takes 504GB on disk, contour lines are already in epsg:3857, and simplified with ...
0
votes
0answers
55 views

Union geometries from multiple tables

I need to create a "total" polygon by merging together all the geometries from about 10+ tables into one big polygon. For a test with just 2 tables I tried ST_UNION and ST_COLLECT, the result was ...
1
vote
1answer
51 views

ogr2ogr Set Feature to Column Name?

I'm converting PostGIS data to PDF via GDAL 2.0.2 with below. ogr2ogr -f "PDF" mypdf.pdf PG:"host=domain.com user=me password=mypassword dbname=mydb" -sql "SELECT geom, state_name, sub_region, ...
1
vote
1answer
45 views

How to speed up raster2pgsql?

I'm trying to upload a 37 GB raster dataset to PostGIS. The raster is out-of-db (-R option), so the actual pixel values are not stored in PostGIS. I'm using this command: raster2pgsql -s 25833 -C -I ...
1
vote
1answer
59 views

Change SRID of geometry column

I have in my table column geom with geometry, data type of this column is geometry(Point,102067). I want to change SRID from 102067 to 5514, but if I use this command select ...
3
votes
2answers
108 views

Alternative to ST_Union/ST_MemUnion for merging overlapping polygons using PostGIS?

New to PostGIS so I trust this is a very basic question but can't seem to find the right solution. I have a materialized view consisting of ~4 million polygons. Some of them overlap. I need to merge ...
0
votes
0answers
19 views

Using the raster function ST_Clip?

I am using Postgis Raster in POSTGIS 2.1.5 on PostgreSQL 9.4.5 on Ubuntu 15.04 32-bit system. I using ST_Clip to clip a huge number of GeoTiff Files by the geometry field of another POSTGIS table. In ...
4
votes
2answers
126 views

Exposing (PostGIS) spatial operations as web services?

How can (PostGIS) spatial operations be exposed as web services? e.g. if we wanted a web service end point that would return a data set with the top 5 schools nearest to a location NOTE: can I make ...
7
votes
1answer
1k views

Could not load library “C:\…\ rtpostgis-2.2.dll”

I've installed PostgreSQL with Postgis (tried both version 9.4 and 9.5 ) on a remote desktop running Windows 7 64bit. However, when I executed this SQL command: "CREATE EXTENSION postgis" (on ...
3
votes
1answer
57 views

Is it possible to display foreign tables (Oracle to PostGIS) in QGIS?

I have used oracle_fdw to create a foreign data wrapper in my PostgreSQL database to connect to my Oracle database and retrieve foreign tables from the Oracle database into my Postgres database. This ...
3
votes
0answers
45 views

Styling using mi-style column from mapinfo

I have uploaded some MapInfo tables into postgis including the mi-style column using EasyLoader; I have had to rename all columns including both mi-style and sp-geometry columns to lowercase, ...
1
vote
0answers
27 views

Saving styles to database

In QGIS, the option to save the style to a database (postgres) does not appear in the drop down list if the postgis table has been opened using add vector layer and then entering the postgres database ...
1
vote
0answers
22 views

How can I use ST_Contains/ST_Overlaps with non-simple lines?

Following the SQL of the accepted answer to How to get smallest line segments from intersection + difference of multiple overlapping lines? copied below. I'm having some difficulties assign the ids of ...
0
votes
1answer
64 views

How to USE ST_difference AND ST_intersection in case of multipolygons ? (POSTGIS)

I try to use ST_difference and ST_intersection with two layers of multipolygons. I want something like: With the input on the left and the result on the right. MY DATA: t1.geom contains 1000 ...
1
vote
0answers
41 views

Is PostGIS / PostgreSQL UK Public Sector Network (PSN) compliant?

Is PostGIS / PostgreSQL UK Public Sector Network PSN compliant? ... specifically so it can be used in a UK local government council?
5
votes
0answers
98 views

Finding density centroids within polygons in PostGIS

I want to find the highest density of OpenStreetMap nodes within a dataset of census geometries. I want to tie the census data to the location of the node which is most central in terms of population. ...
0
votes
2answers
75 views

Routing using OSM data, osm2po, and pgrouting: strange route produced

I downloaded latest data from Geofabrik and ran the data through osm2po. osm2po web service works fine. I tried to find the route from node 103843 to 170597 and got the following route: However, ...
0
votes
1answer
48 views

How to use gdal_rasterize with postgis vector?

I'm trying to convert a multipolygon vector layer to a raster using the GDAL tool gdal_rasterize. The command I am using is: gdal_rasterize -b 1 -burn -9999 PG:"dbname=db password=password" \ ...
0
votes
1answer
36 views

Adding a column to polygon table with correct corresponding address

I have 2 shapefiles: one with polygons (buildings) and their properties other one is a point address shapefile - it contains points that are inside polygons from the other SHP, but it's still ...
2
votes
1answer
30 views

Why am I getting a syntax error at “RETURN result;” when creating a CartoDB pl/pgsql function?

Despite CartoDB appearing to run PostGIS 2.2 (see below), it still doesn't support multi-point geometries in ST_Split. SELECT postgis_full_version(); POSTGIS="2.2.0" GEOS="3.5.0-CAPI-1.9.0 r4084" ...
1
vote
1answer
63 views

orthogonal projection of a point onto linestring in PostGIS

I am trying to find the orthogonal projection of a Point (in 4326 SRID) onto a LineString (in 4326). The picture shows the objective. I have tried to use ST_Distance function, but in this case the ...
0
votes
0answers
45 views

Retrieving postgis image to nodejs?

I am trying to retrieve a PostGIS image generated with ST_AsPNG to nodejs-pg-promise then put it to respond. I have achieved the PostGIS side, but have failed on putting the resulting image to ...
0
votes
0answers
26 views

Using pgsql2shp in server code with PostGIS installed on Amazon RDS instance

I've created a web application using the Meteor web framework, GeoServer and PostGIS. I've deployed my app on Amazon Web Services with the Meteor app and GeoServer running as 2 separate tasks on 1 EC2 ...
3
votes
1answer
31 views

How to quickly load two distinct areas into postGIS without using --append flag?

I have two distinct / non-overlapping areas that I wish to load into a postGIS database. The trouble is that once one area is loaded, it becomes painfully slow to load the second area using the ...
0
votes
0answers
33 views

ogr2ogr - PostGIS to GML with nested attributes

I am attempting to create GML from PostGIS tables and having issues with nested tags. Before I waste too much time on this, I would like to know if what I am trying to do is possible with "ogr2ogr". I ...
0
votes
1answer
29 views

SQLAlchemy Error on insert

I am trying to add a point with GeoAlchemy 2. However I am getting getting error on insert action saying ---sqlalchemy.exc.InternalError: (psycopg2.InternalError) parse error - invalid geometry ...
0
votes
1answer
39 views

Converting files with ogr2ogr and dropping ALL attribute fields

I want to add a gpx file to a PostgreSQL/PostGIS table using ogr2ogr, but my table does not have any of the fields that are attributes of the GPX file. There are previous questions here dealing with ...
1
vote
1answer
58 views

PostGIS help writing a ST_MakeSimple method for self intersecting lines?

I have imported Open Street Map lines into PostGIS 2.1 and I need to clean up the self intersecting ways. I have done some manual edits in the past but there are many more ST_IsSimple(way) = false ...
-3
votes
1answer
45 views

ERROR: syntax error at or near “group” LINE 4: …E ST_DWithin [closed]

I'm writing an SQL query but I am getting a syntax error on the line with the GROUP BY. What can possibly be the problem? UPDATE intersection_points i SET nbr_victimes = sum(tue+bl+bg) FROM ...
0
votes
0answers
20 views

Geoserver Training Material Error - Installing WPS and wms-eo plugin

I have installed Geoserver training package from geo-solutions. Following this link - Tutorial from geo solutions Works perfectly. Though when I am trying to add Web Processing Service (WPS) ...
5
votes
1answer
107 views

How to get smallest line segments from intersection + difference of multiple overlapping lines?

Inspired by this question, and this excellent answer. I have multiple overlapping lines and want to extract the complete intersection of them. So from this sample data SELECT id, geom INTO stackex ...
2
votes
2answers
149 views

points within a polygon

Since I am a beginner to postgis I am facing problem with finding points within polygons from my table. This is my table structure Column | Type | ...
2
votes
0answers
24 views

Can't delete postgis

geo2=# create extension postgis; ERROR: PostGIS is already installed in schema 'public', uninstall it first geo2=# create extension postgis_topology; ERROR: required extension "postgis" is not ...
0
votes
0answers
13 views

Performance of PostGIS view vs shapefile for WMS in GeoServer? [duplicate]

What does GeoServer render as WMS faster, a layer using a postgis view or one getting data from a shapefile? The data I am working with is a table with 55k rows and with 30 columns, with multipolygon ...
-3
votes
1answer
36 views

Storing PostGIS view for GeoServer? [closed]

I want to know, where this view is actually stored in the server, and how does it interact with the database. I saw that the size of the database is increasing when GeoServer try to serve/create some ...
2
votes
1answer
19 views

How to polygonize using GeoDjango and PostGIS?

The code of GeoDjango-1.9.2 contains reference to the function ST_Polygonize at: https://github.com/django/django/blob/master/django/contrib/gis/db/backends/postgis/operations.py#L132 But is it seems ...
6
votes
1answer
111 views

Direct Connection from Postgres to ArcGIS results to two feature classes per table

I'm using ArcMap (10.3.1) to connect to Postgres (9.4.5), which has Postgis version 2.2.0 installed. Create/Enable Enterprise Geodatabase wasn't used in this database. It's just a connection to the ...
0
votes
0answers
37 views

Calling cmd command from stored procedure in PostGIS

How to execute the command prompt from stored procedures in PostgreSQL. Below is cmd command to convert PostGIS data to image: PG:"host='<host>' port:'<port>' dbname='<dbname>' ...
0
votes
1answer
100 views

run command line shp2pgsql in c# function?

i'm developing an application web with asp.net/c# and i would in a button importing the shapefiles into postgis, and i'm trying with this code, this code i'm using it in cmd.exe in windows it imported ...
0
votes
2answers
59 views

PostGIS: MultipolygonZ to floating point raster

I am trying to convert a table of MultipolygonZ geometries to a floating point raster where the pixel values of the output raster will be derived from the Z values of the MultipolygonZ geometries. The ...
0
votes
0answers
27 views

Converting PostGIS data to image [duplicate]

I have an image showing the height information of a map. I have created a table using the 'raster2pgsql' command. This table stores the height values of the map. Now I want to create an image out of ...
5
votes
1answer
62 views

What is the best way to merge lots of small adjacents polygons ? (POSTGIS)

I am looking for a way to merge lots of small adjacents polygons. I have this kind of features: So if my polygons are adjacents AND have the same color attribute then I want to merge those ...
3
votes
1answer
67 views

Optimising a very large point in polygon query

I have a national dataset of address points (37 million) and a polygon dataset of flood outlines (2 million) of type MultiPolygonZ, some of the polygons are very complex, max ST_NPoints is around ...
0
votes
0answers
69 views

Converting PostGIS data to an image

I have an image showing the height information of a map. I have created a table using the 'raster2pgsql' command. This table stores the height values of the map. Now I want to create an image out of ...
2
votes
1answer
292 views

Spatial clustering with PostGIS including attributes

Spatial clustering with PostGIS including attributes I have a points table with some attributes like : year, type, etc. And I'd like to do a points clustering in PostGIS. I followed the subject in ...
1
vote
1answer
54 views

Duplicate entries in table list when adding PostGIS layer in QGIS

When adding PostGIS layer in QGIS (version does not matter) i observe duplicate entries in the list as can be seen below (e.g. ax_gebaeude, ax_flurstueck, ax_gehoelz...), one with a valid geometry, ...
4
votes
1answer
46 views

Find polygons that does not contain (any) polygons with PostGIS

New to PostGIS, I have a polygon layer of water catchment areas which are formed by the specific monitoring points along the river upstream. For example, on one river, there could be multiple areas, ...