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

learn more… | top users | synonyms

6
votes
1answer
107 views

Leveraging capabilities of PostGIS for large (ish) tables in PostgreSQL?

I am using PostgreSQL and PostGIS to do some exploratory analysis and eventually spatial analysis. I have a table with 40M+ points (geolocated tweets) in the United States that has a spatial index. I ...
3
votes
1answer
44 views

PostGIS: floating point representation problem with ST_AsText

I have a table with geometries, all with about 13 digits of accuracy. While trying to represent them with ST_AsText though, most vertices come out in decimal notation, but some come out in scientific ...
2
votes
1answer
22 views

Extracting the geometry of the parts not intersecting from 2 shapes overlapping each other in postgresql?

This seems simple enough, yet i do not find a clear awnser on that peculiar matter. What i am trying to do is to extract the parts of a shape that doesn't intersect with another when the 2 of them are ...
2
votes
1answer
29 views

PostGIS - default spatial reference and Z value on insert or geometry update?

I have a spatial table with a geometry column geometry(MultiPolygonZ,4326). We are trying to handle inserts and updates of geometries as GeoJSON from other systems of varying vintage. Everything is ...
0
votes
1answer
33 views

Why do I get a 503 error when pushing data to the CartoDB SQL API

I'm using ogr2ogr to push a large (200,000 feature) dataset to the CartoDB API. I'm using where_clauses to upload 500 features at a time, and sleeping every 20,000 features for a minute to give the ...
0
votes
0answers
37 views

Using pgRouting through PostgreSQL

I followed the guideline from this tutorial to use pgrouting through PostgreSQL but I can't understand the first instruction: CREATE OR REPLACE VIEW road_ext AS SELECT *, st_startpoint(the_geom), ...
0
votes
0answers
21 views

Can you merge GeoJSONs in PostGIS?

I'm not too familiar with the GeoJSON functionality in PostGIS. In essence - I'm having problems with the computation of ST_AsGeoJSON(ST_Union(g)) taking too long for large g. Is it possible to ...
0
votes
1answer
32 views

How to deal with “ERROR: GEOSUnaryUnion: TopologyException: Self-intersection”?

I'm using Postgres 9.4 and PostGIS 2.1. My table looks like this: ------------------------------------------------------------------ ogc_fid | integer | not null default ...
2
votes
1answer
40 views

For Loop Maximum Minimum Distance Between Two Lines

I have a table of line/lane ids in a table called matched_lanes, and I am trying to create a lateral query that on each id in the table to find the maximumum minimum distance between each point. ...
0
votes
1answer
30 views

CartoDB - inserting polygons

I am new to using PostGIS and CartoDB, and I am doing some test SQL inserts into both using PHP. I've had no problem with points, but polygons are proving to be a bit tricky - my SQL insert works ...
1
vote
0answers
27 views

pgRouting “Can't find Table”

I am using pgRouting version 2.1.0. I am using pgAdmin III. I have created a street network using Anita Graser's guide: https://anitagraser.com/2013/07/06/pgrouting-2-0-for-windows-quick-guide/ But ...
1
vote
1answer
37 views

Getting node information of a way in OSM2PGSQL data

I have a feeling this is not possible, but I am looking to get information on the nodes which are part of a line in the planet_osm_line table. The issue which I am trying to handle is I am trying to ...
0
votes
1answer
24 views

saving results of ST_offsetcurve to new table with geometry and fields from original data table

I am trying to create lines parallel to some road centerlines, using distances from the attribute table specific to each line. I'm trying to figure out how to get it to save to a new table with ...
1
vote
1answer
21 views

Geoserver getfeatureinfo returns big number with exponential format

I have a Geoserver layer, which contains numbers on a million scale in one of the attributes. If I query these features, I get back a number in this format: 4.1E7 instead of this: 410000000 Is there ...
1
vote
2answers
25 views

Assigning variables into a API text string postgis

I am trying to assign values to variable in PostGIS. I have a API similar to: example I want to create a trigger that parse the x,y coordinates from my geometry column to a field containing the ...
2
votes
1answer
36 views

Export to DXF from PostgreSQL using ogr2ogr

I have a large dataset in a PostGIS database (OS MasterMap). My aim is to export specific areas of this dataset to DXF, while retaining the attributes of the data as layers in the DXF. My current ...
1
vote
0answers
19 views

How to add large file (in GB) in Xcode project?

I am working with Mapbox Framework. I have generated MBTiles file which I want to use in my project (for iPhones and iPads) but that file is having size of 3GB (which I can't use in project). When I ...
2
votes
3answers
64 views

Checking if a polygon contains multiple point geometries using PostGIS?

This is not a a duplicate of the "Count Points in Polygon with Postgis", since in the ST_Contains(geometry geomA, geometry geomB), this case has two geometries (start_geom and end_geom) in geomB, ...
0
votes
2answers
54 views

Casting between PostGIS types in GeoAlchemy

I'm working with OpenStreetMap data, and loading it into a PostGIS database using osm2pgsql. The issue that I am currently dealing with is that osm2pgsql stores the shape as a geometry type. I'm ...
1
vote
0answers
29 views

Python GDAL open dataset in PostGIS with quotations

Error trying to use gdal to open a dataset in PostGIS with the following code: ds = gdal.Open("PG: dbname=nyc host=localhost user=postgres password=mypassword port=5432 mode=2 schema=public ...
1
vote
0answers
27 views

PostGIS Svg Projection on Google Map

Context I have a database with spatial data such as areas and points. Some of the areas in my db are coming from OpenStreetMaps, representing some regions. (Which are kinda small at earth's scale, ...
0
votes
2answers
31 views

Building efficient multi geometry type database with PostGIS?

I'm trying to build a PostGIS database which must be able to store (multi)points, (multi)linestrings and (multi)polygons. The goal is to efficiently querying the data with ST_Intersects and other ...
2
votes
1answer
38 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
35 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
34 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 ...
2
votes
1answer
40 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
63 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
36 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
26 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
67 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
2answers
32 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
17 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
60 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
27 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
46 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
148 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
29 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
86 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 ...
0
votes
1answer
40 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
52 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
46 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
32 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
28 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
31 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
82 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
56 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
46 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
49 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
vote
1answer
33 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)' ...