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

learn more… | top users | synonyms

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
4answers
1k views

Are there open source solutions for travel demand modelling?

I'm looking for open source tools for travel demand modelling (eg The four-step UTMS). Preferably in a GIS environment. That works well with Qgis, Grass and Postgis. My question is not related to ...
6
votes
2answers
1k views

Storing GPS tracks with timestamps in PostGIS

I need to store a series of GPS points with timestamps in the database (traces of various vehicles). I am wondering which of two approaches to choose for storing them in the PostGIS table: 1.) Each ...
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
2k 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
629 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 ...
5
votes
1answer
1k views

grib2 to raster? (grib2 -> raster2pgsql -> PostGIS 2.0 -> GeoServer)

I'm looking to implement import of US National Weather Service data for doing various sorts of models (www.soils.wisc.edu/uwex_agwx if you're curious about what we're doing with the data). The ...
4
votes
2answers
672 views

How to group connected linestrings in PostGIS?

I have a table of streets I've selected based on a set of attributes (let's say it's speed_limit < 25). There are groups of streets that are locally contiguous; I'd like to group these sets of ...
3
votes
1answer
182 views

nearest facility with pgrouting

I have a postgres table of students with lat, lon and geom, , and a table of schools with the same, and a table of roads that has been converted to nodes. I'm trying to find the driving distance to ...
3
votes
3answers
539 views

Offline Map Service with Mapnik and Leaflet

I'm relatively new to GIS, so I hope my asking doesn't appear to be ignorant in any way. I currently work on an offline map service solution for usage in areas without internet connection. For that ...
3
votes
1answer
101 views

Nearest neighbour for every row

I'm relatively new to PostGIS and postgresql . I'm trying to build a simple query containing two relations as follows : I've two relations R1 and R2 both of which contains geometries(Points) . I ...
3
votes
2answers
718 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
151 views

How to split polygons with another polygon and inheritage data?

Let's say that we have two tables with polygons representing areas. Table A and Table B. Geometries from table A represents both the valid and invalid areas. And the geometries in table B represents ...
2
votes
2answers
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 ...
1
vote
1answer
874 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, ...
12
votes
2answers
542 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 : ...
10
votes
1answer
1k 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
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
7k 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
3k 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
3answers
350 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?
7
votes
2answers
677 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 ...
7
votes
3answers
3k 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 ...
7
votes
2answers
597 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 ...
7
votes
3answers
4k views

Bounding box for PostGIS table

Is there an easy way to get the bounding box for an entire table in PostGIS? Thanks
6
votes
2answers
242 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
5answers
5k views

Open Source equivalent to Collector for ArcGIS to use with QGIS/PostGIS?

From my reading of the Esri web pages the Collector for ArcGIS is tightly tied to that platform. I would be delighted to learn that I am wrong about this. I have a strong interest in generic mobile ...
6
votes
1answer
1k 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
418 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
2answers
401 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
697 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
5k 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
3answers
3k views

When should I use ST_Buffer?

I would like to know what is the sense of using st_buffer operation. In PostGIS reference guide there is only the syntax and a short description of this operation. Could you give some examples to ...
6
votes
2answers
840 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
3answers
538 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
0answers
2k views

Loading raster from PostGIS DB into QGIS: succeeded yesterday, fails now [closed]

I have a PostgreSQL(v.9.2)/Postgis(v.2.0.3) database which includes raster layers (added through raster2pgsql). To open them in QGIS (v.1.8.0-Lisboa), the "Add Postgis layer" menu will not work; ...
5
votes
1answer
485 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
583 views

Adding a GIS feature to existing database entry with QGIS

I've built a database with postgreSQL with tables, which include geometry columns. When I add some data to my non-geometry columns, I can see them in QGIS when I connect to my database using postgis. ...
5
votes
3answers
3k 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 ...
5
votes
3answers
4k 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
808 views

Split edges at intersections in PostGIS

I'm attempting to do a walking distance analysis using OpenStreetMap data (osm2pgrouting) and PostGIS and following underdark's blog posts, however I need to exclude residential and service roads that ...
5
votes
1answer
528 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
2answers
1k 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
318 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 ...
4
votes
3answers
1k views

How to speed up Postgis raster layer display in QGIS?

i've been importing some digital orthophoto geotif files into postgis 2.0.1 via raster2pgsql tool (i also made 3 levels of an overview table - pyramid). Then i tried to import the layer into qgis via ...
4
votes
1answer
1k 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

Convert KML to shapefile without losing attribute data

So I want to convert KML files to SHP shapefiles to load it onto PostGIS. Currently I'm using ogr2ogr to do the conversion for me, but it wipes out all the attribute data. How do i preserve this ...
4
votes
3answers
676 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
145 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
578 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 ...