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

learn more… | top users | synonyms

1
vote
1answer
24 views

Identify groups of stacked geometries in PostGIS

I need to create a column in my PostGIS table with values identifying different groups of stacked/duplicate geometries, such that one group of stacked geometries might get a value of 1, another group ...
1
vote
1answer
36 views

PostGIS clipping raster with the same color band as the original raster

I am trying to clip a raster so that the clipped raster would have the same minimum and maximum values for the color band as the original raster. For example if i clip the raster with this command: ...
0
votes
1answer
48 views

ogr2ogr - Clipping postgis and creating geojson

I'm trying to create a geojson file data based on data from my local postgis database using ogr2ogr. I'm trying to clip to a specific area and when I start to run the command, everything starts fine, ...
1
vote
0answers
25 views

Finding the shortest path between points to LineString PostGIS

I have a MultiPolygon. Something like this: All one colored circles make up one multipolygon. I would like to find the shortest line to connect all one colored polygons. The first idea was to find ...
2
votes
1answer
39 views

Add SRID to Point

When inserting a point into a PostGIS database I do it using ST_GeomFromText('POINT(0 1 2)',26918). Note that this was done on a tutorial database where the SRID was set manually as explained ...
1
vote
0answers
23 views

Problem with previewing layer on geoserver ( instead of downloading wms file ) [closed]

When I hit OpenLayers to preview my layer in Geoserver, instead of opening a new tab with OpenLayers it automatically downloads a wms file I just opened the wms file and says : <?xml version="1.0" ...
1
vote
0answers
19 views

Configure new SQL view doesn't appear for different DB's in the same PostGIS Database

I have PostGIS Database with different DB's. Each one of them has different workspace:store in Geoserver. When I'm opening Create new layer page and picking different workspace:store for some of them ...
1
vote
1answer
32 views

Postgis bounding box intersect vs polygon intersect

I am running a point in polygon test for close to one billion points. The size of my polygons are very small (at about 12 vertices each polygon). Therefore, I would be satisfied even with a bounding ...
1
vote
1answer
67 views

Create Table with postGIS geometry in Python

I'm creating the following table with a postGIS geometry column in python but get an error psycopg2.ProgrammingError: type "geometry" does not exist What should I use to be able to use postGIS in ...
1
vote
2answers
46 views

Conditional “On the Fly” Column with PostGIS

I have two tables I am working with; tl_2015_us_county which consists of county polygons, and tl_2015_48_puma10 which consists of PUMA polygons. I am currently using the below SQL in order to achieve ...
1
vote
1answer
48 views

How to create a polygons (squares) from point

I want to create a regular grid of polygons/squares from this point knowing that between two successive points 100 meters, so each square would have 100*100 m2 in its area.
0
votes
0answers
23 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
51 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
162 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
33 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
50 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
49 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
36 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
50 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
86 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
18 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
118 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
53 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
38 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
22 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
19 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
53 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
92 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
63 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
38 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
34 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
28 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
59 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
39 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
25 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
30 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
22 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
36 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
51 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
36 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
15 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
102 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
23 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
33 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
18 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 ...