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

learn more… | top users | synonyms

0
votes
0answers
22 views

pgrouting floyd-warshall with spesific start & end

I'm implementing floyd-warshall algorithm for the shortest route in the road network using pgrouting plugin. how to determine the specific start and end points?
0
votes
0answers
27 views

How cut multilinestring by 2 points on postgis?

I know how cut linestring by 2 points, easy: ST_LineLocatePoint,ST_LineSubstring. But i have unmerged multilinestring, few lines are not connect?
0
votes
1answer
60 views

How can i convert the coordinates (latitude,longitude) to X,Y using postgis

I want know how i can convert the latitude and the longitude coordinates of a rectangle in the Cartesian coordinates, so I can store them in a geometry field using postgis.
1
vote
1answer
36 views

Clustering markers using quadtree

I have a POSTGIS database with geography points and i want to cluster the points using quad-tree . I have a map where i have to display the markers clustered or non clustered depending on the zoom ...
2
votes
0answers
42 views

QGIS : How to calculate centroid on polygons with holes

I try to compute the centroid of polygons with holes on qgis. I did not manage to do it directly, I had to make a conversion to convex hulls before extraction. I crashed my pgsql server trying to ...
1
vote
1answer
45 views

Postgre/PostGIS - How to find points inside an area

I have a database of clients/users with their coordinates. I would like to select all clients within the marked area. Query: SELECT * FROM clients INNER JOIN polygons ON polygons.polygon @> ...
0
votes
0answers
36 views

Are there any addresses that straddle two or more congressional districts?

I'm using a shapefile of TIGER data provided by the Census Bureau for locating an individual's congressional district. In PostGIS I'm running an ST_Intersects query in order to take a (lat, long) ...
-1
votes
0answers
45 views

Implementing spatial autocorrelation (global and local moran's) using PostGIS?

Has anyone tried to count global and local moran's index for polygons inside PostGIS database, with defined neighborhood relations as queens? Is it even possible? I could use R, but my goal was to ...
1
vote
1answer
31 views

How to check point intersection with bbox made from two points?

I have two points which represents lower left and upper right point of BBOX. I have one point which SHOULD be somewhere inside and I want to test it. I have all of them as 'geometry(POINT, 4326)' ...
0
votes
0answers
42 views

How can I add the PostGIS extension?

I'm experiencing difficulties when trying to add the PostGIS extension to PostgreSQL. I wasn't able to add the extension during the installation because of an error. Now I'm trying to add the ...
3
votes
1answer
53 views

OSM roads polygonized in PostGIS = nonsense

I have a dump of OSM data in my city and wrote a query to polygonize it. In the first attempt, the result looked like this: The problem was that OSM lines were not split and I guessed that the ...
1
vote
1answer
25 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
37 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
26 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
24 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
68 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
47 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
49 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
24 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
52 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
164 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
52 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
50 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
51 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
87 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
119 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
39 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
66 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
29 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
40 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 ...