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

learn more… | top users | synonyms

9
votes
1answer
1k views

How to add features to a PostGIS layer using Quantum GIS?

I am using Quantum GIS and the PostGIS dbms. I am able to call the PostGIS layer into QGIS but i am unable to add feature to it. I am using the method of adding features to a Shapefile but still it is ...
9
votes
3answers
3k views

Beginner PostGIS question: getting geometry from multiple tables

I am very new to PostGIS, PostgreSQL, and SQL in general. I have 44 tables in my PostGIS database, and each one represents a different layer of vector data. Each was loaded from a separate shapefile, ...
8
votes
2answers
277 views

Find headwater polygons

This is a follow up question to this question. I have a river network (multiline) and some drainage polygons (see picture below). My goal is to select only the headwater polygons (green). With ...
8
votes
2answers
1k views

Using ArcGIS and QGIS together in multi-user editing environment?

We have a GIS staff of 7, and another group of about 14 who "use GIS" from time to time. We really, really want multi-user editing. Mostly of a large point file, and we're almost never in the same ...
8
votes
1answer
511 views

How do I calculate Manhattan distance with PostGIS?

I am using the ST_Distance function to calculate the distance between two geometries (a train station and a building) Since I know that all buildings and all train stations are in Chicago, which has ...
8
votes
1answer
4k views

PostGIS select by lat/long bounding box

I have a minLat, minLong, maxLat and maxLong from a box drawn on a map. Right now, my query to get all the points in the box looks like this: SELECT * FROM geomTable WHERE (longitude BETWEEN minLon ...
8
votes
2answers
1k views

How to add vertices to existing linestrings?

If I have Linestring(1 2, 1 5, 1 9) and a Point(1 3) Is there any function that can merge linestring and point preserving the order so output would be: Linestring(1 2, 1 3, 1 5, 1 9)
8
votes
4answers
861 views

Looking for a basic PostGIS viewer to use as basis for own application

I'm looking for a rather basic PostGIS viewer. It should be possible to pan, zoom and retrieve coordinate information on mouse click. No need to support coordinate transformations. It should introduce ...
7
votes
1answer
836 views

Performance in calculating raster statistics in PostGIS

I'm trying to calculate raster statistics (min, max, mean) for each polygon in a vector layer using PostgreSQL/PostGIS. This GIS.SE answer describes how to do this, by calculating the intersection ...
7
votes
4answers
4k views

How to extract POINT from a [LINE|MULTILINE]STRING

I'd like to create a vectorLayer in QGIS to display all the points that form a LINESTRING or a MULTILINESTRING stored in a PostGIS DB. I think that I need to transform all points of LINESTRING (or ...
7
votes
1answer
2k views

Why do simplify polygon results differ between ArcGIS for Desktop and PostGIS?

I am mainly interested in why and how the Simplify Polygons tool in ArcGIS for Desktop handles the simplification process, including preventing and resolving topological errors better than the ...
7
votes
2answers
2k views

How to run two PostGIS versions in parallel on Windows?

I'm trying to understand how to install PostGIS2.0 on Windows7. The PostGIS site has the following suggestions: PostGIS Experimental Binaries PostGIS 2.0.0SVN Binaries with Raster, Topology ...
6
votes
1answer
118 views

How do I optimise performance on a local QGIS + PostGIS installation?

I'm learning how to manage a local PostGIS (ver 2.1) database, with spatial indexing enabled on the shapefiles that I've imported (using the PostGIS Shapefile Import/Export Manager). I have imported a ...
6
votes
1answer
524 views

Configuring Geoserver to use a specific postgis geometry column as the default

I have a PostGIS table with two geometry columns, both multipolygon but one with SRID 4326, the other 3857. When publishing the table in Geoserver, I realize that Geoserver defaults to using the first ...
6
votes
3answers
367 views

What is the easiest one click install method for PostGIS and it's entire dependencies?

For someone new to database servers and their dependencies, what is the easiest assured way of installing PostGIS on a machine with no other software beyond what comes with its operating system? I'd ...
6
votes
1answer
2k views

Performance Gain Through GIST Index for Point in Polygon Query

I have two tables: locations(id,region_id,the_geom) and regions(id,the_geom). For each location point I want to determine the region it is located in: UPDATE locations SET region_id = (SELECT id ...
6
votes
1answer
3k views

How to measure length of line accurately?

In postgis 1.5 for the below given query the answer is 1540651.62674101 meters , SELECT ST_Length(ST_GeomFromText('LINESTRING(77.83042478941326 9.98463896136159,78.03469506809812 23.90398909552571 ...
6
votes
3answers
1k views

Is the Fourth Dimension used in GIS?

Whilst working with OGR, I came across the following field used define a geographic dataset: COORD_DIMENSION The spatial dimension (2, 3 or 4 dimensional) of the column. Whilst I've used 2D and 3D ...
5
votes
2answers
2k views

Postgis 2.1 error after update

After a usual update progress - "apt-get upgrade" - I can not connect with qGIS to my PostGIS DB anymore. Error in the qGIS logfile: Your database has no working PostGIS support. Erroneous query: ...
5
votes
3answers
4k views

What's the fastest way to do a bounding box query in PostGIS

I have a Postgres table, with almost 2 million rows, with a long-lat coordinates field in the form POINT(-73.4938 33.2405). Supposing there's a geospatial index on that field, what's the most ...
5
votes
2answers
1k views

How to detect duplicates and keep only one?

I've got a table with lots of points (200.000). Some of them are duplicates (not exactly but e.g. a metro station has eight entrances) and I want to keep only one of these metro entrances. I am able ...
4
votes
2answers
259 views

How to use interacting PostgreSQL/PostGIS triggers in QGIS?

I use point geometries (table 'label_point') to place multiple labels on polygon geometries (table 'soil'). After doing layer edits I've to update the affected label_point features. Therefore I've ...
4
votes
3answers
3k views

PostGIS: parse geometry wkb with OGR

I'm trying to pull a LineString geometry out of PostGIS and parse it with OGR (python bindinds). from osgeo import ogr import psycopg2 connection = psycopg2.connect("...") cursor = ...
3
votes
1answer
539 views

PostGIS - when I add a polygon, delete overlapping areas in other layers

I'm starting to work with PostGIS and I'd like to solve the following problem: In my database, I have multiple land use class layers, for example: "residential", "industrial", "commercial". The ...
3
votes
1answer
3k views

Where to find shp2pgsql windows binaries?

Is there anywhere available shp2pgsql binaries for windows without installing the whole PostGIS? I particularity need the command line version of that import tool. I would like to share a batch ...
3
votes
2answers
939 views

How to create a valid global polygon grid in PostGIS?

I want to create a grid spawning the whole globe. The grid is used to measure a density value by counting the number of certain spatial objects in each grid cell. To create the grid, I have created ...
2
votes
1answer
2k views

Attempting to use ST_DWithin to locate objects within a given distance of meters using SRID 4326

I'm a Rails developer with minimal GIS experience, working with PostGIS and the RGeo gem. I'd like to find all of the cows within 500 meters of my current location. So, I'll create a model for my ...
2
votes
1answer
839 views

Get the lines between all points of a polygon in PostGis : avoid nested loop?

I am trying to get all the lines between the points of a polygon, by writing PostGreSQL functions. I have written the two following functions that do the job fine : the first function to get all the ...
1
vote
1answer
859 views

Will MapInfo only read PostGIS data that has been loaded via EasyLoader?

NMZ says in this thread that MapInfo will only read data in PostGIS that has been loaded by MapInfo's EasyLoader. I wasn't sure if this was still the case so I've loaded data into postgis via ogr2ogr ...
1
vote
2answers
1k views

How to improve poor performance times for PostGIS 2.1 Tiger Geocoder?

I've recently upgraded my PostGIS DB from 2.0 to 2.1. I initially followed the instructions from Waiting for PostGIS 2.1 - Install PostGIS Tiger Geocoder as an Extension and 2.7. Installing, ...
14
votes
1answer
13k 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 ...
13
votes
6answers
7k views

QGIS, Postgis: Geometry type does not match column type

I'm trying to import some polygon shapefiles to Postgis in QGIS via SPIT. One of them can't be imported and returns this error: ERROR: Geometry type (Polygon) does not match column type ...
12
votes
2answers
585 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 ...
10
votes
2answers
2k views

GUI tools for PostgreSQL/PostGIS?

as a cartographer I work with TileMill to visualize geodata. I installed a local PostgreSQL server with PostGIS and osm2pgsql. Everything works fine. Anyhow, I am new to working with servers and the ...
10
votes
2answers
10k views

Creating spatial tables with PostGIS

In the PostGIS documentation it says that there are two steps to creating a spatial table with SQL: Create a normal non-spatial table. Add a spatial column to the table using the OpenGIS ...
9
votes
1answer
208 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
3k 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
398 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
739 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
3answers
4k 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
382 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
2answers
362 views

How to replicate ArcGIS Intersect in PostGIS

I'm trying to replicate this ArcGIS process in PostGIS: http://blogs.esri.com/esri/arcgis/2012/11/13/spaghetti_and_meatballs/. This describes how to break buffered points down into polygons based on ...
6
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. ...
6
votes
1answer
498 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
474 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
817 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 ...