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

learn more… | top users | synonyms

2
votes
1answer
37 views

ST_ClosestPoint equal, but not touching

I have two polygons where one should cover the other. They were created in qgis using snapping, however when I checked their topology with postgis it turned out one point on the inner polygon ...
1
vote
1answer
32 views

Find the convex hull around a set of polygons?

I'm using Postgres 9.4 with PostGIS 2.1. I have a table with the following columns: Column | Type --------------+------------------------ ogc_fid | integer ...
0
votes
2answers
26 views

shp2pgsql: how to know the schema.table?

I want to import a shapefile using shp2pgsql. I am using Mac with Postgis 2.2. Based on this reference: A conversion and upload can be done all in one step using a UNIX pipe: shp2pgsql -s ...
1
vote
1answer
38 views

“column does not exist” for SELECT ST_Point

I am using psql (9.5.2) and postgis 2.2.2. In the psql shell, I have created a spatial database and connected to it. I want to insert a point into the postgis geodatabase. To do so, I first create a ...
1
vote
1answer
51 views

PostGIS ST_Intersects GeometryCollection

If i'm not terribly mistaken, ST_Intersects does not support GeometryCollection. ERROR: Relate Operation called with a LWGEOMCOLLECTION type. This is unsupported. HINT: Change argument 2: ...
1
vote
0answers
33 views

How to calculate a Label Point for a (multi)polygon [duplicate]

ESRI software has a concept of a 'label point' for a (multi)polygon. It is like the centroid, except it is guaranteed to be within the (multi)polygon. Like for a U-shaped polygon, the label point is ...
0
votes
0answers
19 views

Handling empty PostGIS geometries with GeoServers Pregeneralized Features extension

I've setup my GeoServer instance (2.8.2) with the Pregeneralized Features extension to serve several large polygon layers from PostGIS (9.3), and while it is working I am still experiencing remarkably ...
3
votes
1answer
59 views

Big datasets in QGIS

I'm working with large PostGIS tables in QGIS but really only need to do simple visualization and editing the attributes (like change road names, speed limits, etc). The most processing I need to do ...
0
votes
1answer
21 views

Not able to create postgres Sql file using Osm2po

I am newbie to the GIS community. After downloading the osm2po jar file, I have run following the instructions from underdark tutorial. Also, from Carsten's answer in gis stack exchange, I have ...
0
votes
0answers
13 views

multigtfs and pgr_createTopology

Could anyone know how to create a Topology on imported GTFS data into PostGIS with multigtfs? In fact, pgr_createTopology needs the edge_table or network table. Here are tables obtained after the ...
3
votes
1answer
43 views

ogr2ogr: encoding issues when importing from Oracle Spatial to PostGIS

I'm trying to import some spatial tables from Oracle Spatial to PostGIS using ogr2ogr, but I can't figure out why the special characters on the attribute tables get messed up while I'm doing it. My ...
2
votes
1answer
26 views

Is there a way to avoid the re-calculation of the same centroid without creating a new column?

All tables contain polygon geometries. SELECT * FROM table1, table2, table3, table4 WHERE st_contains(table2.geom, st_centroid(table1.geom)) AND st_contains(table3.geom, ...
3
votes
2answers
38 views

Polygon another location

Using google maps I have located 4 points and have created a polygon with them in this way insert into dummy_zip values (4,'prueba_directa','2016-04-13 ...
5
votes
1answer
144 views

Adding new nodes in the middle of each líne of a Polygon

I am currently working on a Street Lighting Network. I have two layers in PostGIS ( PostGIS 2.1.3 and PostgreSQL 9.3.5): Manholes (56.000 Polygon) Pipes (Linestring) The manholes are perfect ...
1
vote
2answers
22 views

Counting points within range using CartoDB?

I have a dataset of users location and want to assign per user how many other users they have within a given range, and updating this value into a table.The dataset name is "users_20160410" and the ...
2
votes
1answer
58 views

How to use ST_ClusterWithin with geography data type and measurement units?

I want to clustering locations use ST_ClusterWithin function: geometry[] ST_ClusterWithin(geometry set g, float8 distance); My table has column location of geography(Point,4326) type and I don't ...
-3
votes
0answers
10 views

How R read and get data from PostGis tables? [duplicate]

I have not more details to add, I need to receive a reply to question above if it is possible
0
votes
1answer
29 views

PostGIS 2.1 ST_EstimatedExtent returns error

In absence of statistics (empty table or no ANALYZE called) the function ST_EstimatedExtent(schema,table,geom_column) should return NULL (PostGIS 2.1). In my case, the function returns FEHLER: ...
3
votes
0answers
43 views

Interpolating point to nearest polygons to compute street width in posgis

I am trying to calculate the width of streets (line.shp) in postgis using reference point (ref_point.shp) (yellow point features) and building polygons (building.shp). The red point features are the ...
1
vote
1answer
41 views

PostGIS: how to merge contiguous features sharing same attributes values?

I have a big postgis table storing line features. I woud like to merge features that are both connected to eachother and have given attribute values identical. Here is below an example of what I'd ...
0
votes
1answer
27 views

Issues with raster data on postgresql / error:Attempting to get pixel value with out of range raster coordinates

So here is the issue, i am trying to extract values from a big raster divided in 200 or so tiles and applying them to a selection. The points are startpoints, endpoints and midpoints from vector ...
0
votes
0answers
17 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
33 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
42 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
28 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
38 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
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
36 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
65 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
49 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
35 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 ...