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

learn more… | top users | synonyms

15
votes
1answer
485 views

How to query OSM for all beaches facing west? - QGIS preferred

I want to identify all beaches on this planet (or a given subset of it) that are facing westwards (+/- 20-30 degrees). My idea is to use OSM as the data source. Extracting all polygons that have the ...
15
votes
6answers
10k 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 (MultiPolygon)...
12
votes
2answers
11k 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 "...
11
votes
2answers
3k 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 ...
11
votes
2answers
604 views

How do you protect your PostGIS credentials when using QGIS 1.7?

I'm fairly certain this should be a community wiki, but I don't know how to make it one. So if an admin sees this please change if appropriate. So.....How do you protect your PostGIS database data ...
11
votes
2answers
895 views

Are there any tricks to using a schema other than public in PostGIS?

I'm currently setting up a fresh install of PostGIS 2.0.2 and PostgreSQL 9.1.6 on Ubuntu. I've recently come across some information indicating that using the public schema to store all data is not a ...
9
votes
1answer
910 views

How to Simplify a Line Network Preserving Topology?

I have a Shapefile (consisting of European major roads) with about 250.000 Segments which I have to simplify for pgrouting. But I can't seem to find a way to do that properly. This is what it looks ...
9
votes
1answer
450 views

How to visualize measure values along linestrings in QGIS?

I have a series of LineStringM geometries. The measure value represents slope (or stream gradient) at each point in the geometry. I love QGIS's robust styling options, but I can't find a way to render ...
9
votes
3answers
4k 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, ...
9
votes
1answer
756 views

How to view a 155 MB raster stored in PostGIS?

I am using the following command to load a raster into a postgres database: raster2pgsql -I -C -e -Y -F -s 3086 -I -C -M myraster.tif myraster -F -t 30x30 | psql -U postgres -d database -h localhost -...
9
votes
1answer
4k views

Postgis - Converting 'geometry_dump' data type to a 'geometry' type

I am working with a dataset of property boundaries that has had ST_Union() run on it, thus all the polygons appear as a multi polygon in one row of a table. I would like to separate these out so I get ...
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
1answer
616 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
2answers
279 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
4answers
887 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 ...
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
2answers
2k views

QGIS, Postgis: Is it possible to create a new postgis layer?

Situation: I have a postgis vector lay that has lots of line segments (the features). I want to create a set of new layers each of which contains a subset of the features of the original layer and ...
8
votes
1answer
995 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 ...
8
votes
1answer
5k 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 ...
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 ...
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
1answer
272 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 ...
7
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
3answers
392 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
3answers
2k 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 ...
6
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 ( ...
6
votes
1answer
697 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
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 )'...
5
votes
2answers
2k views

Rasterize a vector layer in PostGIS

Related to this question asked here on gis.se and this thread on postgis-user, has anyone worked out a good solution for in-database rasterization of a vector layer using PostGIS? It sounds like the ...
5
votes
2answers
3k 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: ...
4
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 ...
4
votes
1answer
160 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
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
3k 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 ...
3
votes
2answers
1k 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 ...
3
votes
1answer
717 views

PostGIS Raster in MapServer

I'm having trouble getting a PostGIS raster to load and display in MapServer. I'm not sure, but suspect it's a version problem. My mapfile layer and the error message are shown below. LAYER ...
2
votes
1answer
215 views

GeoJSON: PolyhedralSurface geometry type not supported

I am trying to make a 3D web application which should be able to render 3D buildings (I will be using Cesium, but that is not that important for now). I basically have a database consisting of the 2D ...
2
votes
1answer
986 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, ...
15
votes
1answer
15k 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
4answers
285 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
600 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
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 ...
9
votes
1answer
237 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 ...
9
votes
2answers
338 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 ...
8
votes
2answers
816 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 message:...
8
votes
3answers
424 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
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
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. ...