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

learn more… | top users | synonyms

15
votes
1answer
14k views

How to convert/typecast from Polygon to Multipolygon in PostGIS?

Is there any way to typecast Polygons to Multipolygon type? For example, I have created a circle using ST_Buffer() on a point. The return type of that function is of Polygon type. The problem is that ...
12
votes
4answers
244 views

How to create lines to visualize differences between polygon features in PostGIS?

I've a PostGIS table polygon_b with some polygon features. There is also a table polygon_a which contains the same polygons as polygon_b but with minor changes. Now I want to create lines to visualize ...
12
votes
2answers
596 views

How to clean such a bad shapefile for routing ?

I am trying to clean a shapefile to make it usable for routing with pgrouting, postgis, postgresql. Here an intersection, it seems to be connected but when we zoom …. It is not: Same here : ...
11
votes
2answers
2k views

PostGIS raster value of a lat/lon point

I have a tiff raster in my PostGIS 2.0 database. I want to get the value of the raster at a specified lat/lon point. I have: postgresql-9.1 postgis-2.0-svn with raster support a raster imported ...
9
votes
2answers
290 views

How to make Great Circle Arcs which look good on a Web Mercator map?

I'm trying to create a map, with data indicating some flights, and want to use Great Circle Arcs, to connect the source and destinations. Basically, I want to do something similar to the famous ...
9
votes
1answer
222 views

Point sampling along a pole-wrapping coastline with PostGIS

I am working on a task that requires me to get sample points every 1000 km along coastlines, and have run into a problem with Antarctica. From what I can tell, it seems to be an issue with the use of ...
8
votes
2answers
4k views

Import large CSV file into PostGIS

I am trying to import CSV files into PostGIS. Following this post, I have created tables before. I found other suggestions saying that I can run the copy command. If I run this command: COPY table ...
8
votes
3answers
413 views

Why doesn't pgRouting return the best path?

Let the next portion of a graph: When I use the function shortest_path between the points A and B, I got the blue path. Why does this happen?
8
votes
2answers
794 views

How to catch PostgreSQL/PostGIS errors in GDAL/OGR Python bindings?

Is it possible to catch PostgreSQL output error messages in OGR after a query has failed? For example conn.ExecuteSQL('SELECT * FROM non_existing_table;') In Postgres this returns an error ...
8
votes
3answers
6k views

Bounding box for PostGIS table

Is there an easy way to get the bounding box for an entire table in PostGIS? Thanks
7
votes
3answers
1k views

How to extract mountain peak points from a given digital elevation model?

For past couple of days, i m trying to determine how many mountain peaks are visible (line of sight) from a given location using POSTGIS. The data i have is DEM for Swiss and address locations from ...
7
votes
1answer
2k views

Setting geometry types importing to PostGIS with OGR

I'm using ogr2ogr to import data into a PostGIS database. Each dataset I need to import has (theoretically) the same exact data structure, but it appears that they are not the same geometry types. ...
7
votes
1answer
994 views

Is there a significantly faster way to buffer than arcmap? [duplicate]

I hope this question is appropriate here and I apologize if it is more of a discussion question than a specific technical problem. I have to buffer 150,000 polygons for a real estate project. While ...
7
votes
3answers
5k views

How do I get the area of a WGS84 polygon in square meters?

The following gives me the area of the polygon in square degrees: SELECT ST_Area(ST_GeomFromText('POLYGON((871325.790874952 6105405.3261047,871418.748307692 6105359.72944624,871346.22022442 ...
6
votes
2answers
479 views

PostGIS ST_Union Performance

I'm trying to execute a 'dissolve' operation in PostGIS using the ST_Union command. The input layer is admittedly quite large and complex. By 'large' I mean 57,771 features, with number of vertices ...
6
votes
1answer
865 views

How to achieve equivalent to Dissolve from ArcGIS for Desktop using ST_Union?

I am trying to achieve an equivalent to the Dissolve tool from ArcGIS for Desktop using ST_Union from PostGIS but it seems I am not getting expected result. I have one table which has certain ...
6
votes
1answer
520 views

Clarification of Logical Operators in a PostGIS Database

The following query returns this result: select count(*) from a , b where a.geom && b.geom; count ------- 66514 On a postgis 2 database. What does the logical operator AND ...
6
votes
1answer
1k views

Raster data on PostGIS

I need to publish raster and vector data through GeoServer WMS service and I have already chosen PostGIS as datasource to keep vector data. Now I would like to know what is the best practice to work ...
6
votes
2answers
490 views

Do large vertex counts cause performance issues?

I have a table of several thousand tuples - each tuple has a geometry column. The problem is, these geometries have a large vertex count. The max vertex count is 400, while the average is 200. ...
6
votes
1answer
875 views

Data visualization and maintenance tool

Background Looking to leverage an existing GIS system for exploring organizational data. Architecture The following figure represents a high-level overview of the system's desired features: The ...
6
votes
5answers
6k views

How to load Shapefiles into a remote PostGIS db using command line?

Can we import a Shapefile from a system which doesn't have PostgreSQL installed (client machine) into a system having PostGIS installed? I am getting problem with 'shp2pgsql' and 'psql' since both ...
6
votes
2answers
911 views

How do you calculate lat/lons from a PostGIS table which has a projected coordinate system?

I have a PostGIS table in a projected coordinate system (EPSG: 28355 or MGA94 Zone55). In QGIS (with the PostGIS manager plugin) I calculate a coordinate by performing the following SQL "UPDATE ...
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 ...
5
votes
2answers
8k views

Exporting shapefile to PostGIS database in QGIS

How do I export a vector shapefile to PostGIS database? I am asking what format I have to export it.
5
votes
3answers
781 views

Very poor performance of QGIS and PostGIS2. User error or feature?

I have a large table in Postgis2. It has >500 million polygon, line and point features all happily living in there and the table performs brilliantly in other GIS. I am now trying to just add that ...
5
votes
1answer
734 views

How to findout direction PostGIS

I want to find out the direction from the 2 points from postGIS database. Please see the attached image. I want to find out the direction from the mainPt to any point. But want to get the result as ...
5
votes
3answers
6k views

How to fix performance problem in PostGIS ST_Intersects?

I'm a newbie in postgis and I have a problem in query performance. This my query: SELECT DISTINCT ON (userid) userid ,ST_AsText(position), timestamp FROM table1 WHERE ST_Intersects ( ...
5
votes
1answer
653 views

Promotion radius in miles around venues using PostGIS

Bear with me as I'm new to PostGIS and GIS in general. I also don't know the domain well enough to create a good title for this post! Here is what I'm trying to accomplish: I have Venues in my ...
4
votes
1answer
130 views

Smallworld > FME > PostGIS > QGIS, handling geometry types

I'm migrating data from Smallworld via FME/SpatialBiz Plugin to a PostgreSQL/PostGIS Environment and then trying to open the resulting tables with QGIS 2.12.1. Problem arises, because Smallworld ...
4
votes
1answer
312 views

How to cluster points into clusters of a maximum diameter in PostGIS?

I'm working with transit stops in GTFS. I want to group together stops (N~8000 for a medium sized agency in the USA) which are proximately related in order to reduce the number of dimensions but also ...
4
votes
1answer
902 views

How to determine on what side of a line is a point is?

I want this in order to generate house numbers, odd or even depending on wether a feature is on one side of a street line or not . Thanks EDIT: In the end i think the most portable way is to use ...
4
votes
1answer
2k views

Error creating a spatial database. ERROR : could not load library “/usr/pgsql-9.1/lib/rtpostgis-2.0.so”

I am running PostgreSQL 9.1 with Postgis 2.0 on Fedora 15. When trying to install the raster support, psql -d mydb -f rtpostgis.sql -v ON_ERROR_STOP=1 I get the following error ...
4
votes
2answers
1k views

Merging polygons that intersect by more than a specified amount

I am working with a dataset that contains a subset of property boundaries in a local government area, thus my dataset is made up of several thousand properties but not all are touching. Since it is a ...
4
votes
3answers
823 views

WFS Caching / Back button

I'm using WFS protocol on Vector layer / GeoServer / PostGIS Currently the map is refreshing and calling new requests whenever i do anything like searching or drawing and when i move back to a part ...
3
votes
1answer
133 views

How to add time and elevation dimension attributes to Geoserver rasters?

In geoserver, when I edit a postgis raster layer and click on the Dimensions Tab, I can't enable either of the Time or Elevation dimensions. The following message appears next to each of the ...
3
votes
1answer
108 views

Invalid GeoJSON when inserting data to a CartoDB (PostGIS) table from Leaflet

I've been following along to this tutorial from CartoDB. I create the GeoJSON using: var drawing = "'"+JSON.stringify(layer.toGeoJSON())+"'"; and then inserting using a custom SQL function ...
3
votes
1answer
111 views

Adding vertices to polygon in PostGIS?

For example I have rectangular polygon (4 vertices). I want to add mid-point on each line of the polygon, so in total now I have 8 vertices. And from them I want to build new polygon who will have ...
3
votes
2answers
529 views

Creating polygons from line segments using PostgreSQL and PostGIS

Is there a PostGIS function that creates polygons from multiple line segments? I want to convert the pictured thirteen lines to four polygons. All lines belong to the table "boundary" (gid, geom). ...
3
votes
2answers
2k views

How to edit a PostGIS database using ArcGIS for Desktop Add-In/Extension? (no ArcSDE)

I need to improve an existing solution to be able to do multiuser editing within a PostGIS database from ArcMap. Available for the solution is ArcGIS for Desktop 10.3 [Basic]. (No non-free Extensions ...
3
votes
1answer
358 views

PostGIS - ST_Within or ST_Disjoint performance issues

I have a table of about 150,000 points in PostGIS with a spatial index and a SRID of 27700 (OS). I want to select the points that fall outside England and Wales. I have a multipolygon table with ...
3
votes
1answer
484 views

PROJ4 / PostGIS transformations between WGS84 and NAD83 transformations in Alaska

I'm trying to implement the recommended WGS84 - NAD83 transformation for Alaska using PostGIS. This post: Geographic Transformations in Alaska Between NAD83 and WGS84 indicates the proper ...
3
votes
1answer
946 views

Mapnik + OpenStreetMap + Shapefile with Raster Images + Mod Tile + PostGIS

I am new to GIS. I have a Shapefile (.shp, .shx and .pbf). The .pbf file contains path to PNG files located on my machine only. I was able to save the Shp data to PostGis via Shape2Gis tool. Now ...
3
votes
1answer
590 views

How to install PostGIS correctly with OpenGeo Suite 4.0 community edition on Windows 7?

I have installed the OpenGeo Suite community edition, version 3, onto Windows 7 multiple times in the past, with virtually no problems. With the latest version of OpenGeo Suite (4.0.1) (which ...
3
votes
1answer
277 views

How can I move along a line (or path)?

Given a line (street, railroad, river...) I want to simulate a moviment inside this line. Example: See this picture: Say I'm in "Nagqu" and want to move to "Kunlunqial". In a time (t=0) I'm in ...
3
votes
1answer
684 views

How to merge connected lines with same direction (PostGis)

I am looking for a way to join all lines that are connected, have the same direction, i.e. start point of one is end point of the other, and share some common attributes. In fact, I want to connect ...
3
votes
1answer
331 views

st_clip and gdal_translate: “Error browsing database for PostGIS Raster properties” - Why?

Im using shell-script, postgis2.0 and gdal for trying to clip raster apart, using an overlapping raster and a the_geom-object. At first I create a view to clip the overlapping part of the raster. It ...
3
votes
2answers
2k views

Merge polygons in postgis

I have a hand-digitized coverage stored in postgis. There, I have a few shiver-polygons I would like to get rid of by including them into one of the neighbouring polygons. are there any commands that ...
3
votes
2answers
615 views

How fill length and feature count in PostGIS table for each polygons

I'am new to PostGIS/PostgreSql. I have a database which containts two shape-files : polygonal layer towns and linear layer of roads To calculate the length and feature count of 'Streets' inside ...
3
votes
1answer
704 views

PostGIS: ST_Transform function conversion problem

I'm inserting map data from a GML file with srid EPSG:27700 into a PostGIS database table with srid EPSG:4326. I'm using the ST_Transform function as in this snippet: ...
3
votes
1answer
4k views

convert list of points to line or polygon

I have a list of Points in a PostGIS table. The rows can be grouped by a date something like: myTable id INT date DATE lat NUMBER lng NUMBER geom GEOMETRY(POINT) now I need a table where all ...