Version 2.0 of PostGIS brought a heavy set of new extensions for the PostgreSQL object-relational database, adding greater support for spatial objects.

learn more… | top users | synonyms

20
votes
1answer
1k views

Nearest Neighbor problem in Postgis 2.0 using GIST Index (<-> function)

I'm trying to use Postgis 2.0 new function <-> (Geometry Distance Centroid) in order to calculate, for each row of my table (cosn1), the distance to the nearest polygon of the same class. I was ...
18
votes
1answer
3k views

Comparison of geocoding solutions (such as Gisgraphy & PostGIS 2.0)

As I am sure many of you are aware, PostGIS has finally released Version 2.0 and included within this version they have also built a geocoder (see: http://postgis.net/docs/Extras.html). Although I ...
16
votes
1answer
190 views

Can we create views over a raster table in Postgis2.0?

Is it possible to create PostgreSQL views over a table that contains raster data? I have succeeded in creating the view but the entry in raster_columns is not correct (all the raster info is ...
14
votes
1answer
2k views

How to publish PostGIS Rasters in Geoserver?

What is the best solution to publish postgis rasters using Geoserver? I have spent a lot of time trying to create a Raster datasource using Image Mosaic JDBC but with no luck. Steps performed: 1. ...
12
votes
4answers
9k views

Loading a raster into a PostGIS 2.0 database on Windows

I'm trying to figure out how to load a raster into a PostGIS2.0 database (I have asked previous questions on this topic here and here). I am trying to use the raster2pgsql.exe program provided with ...
11
votes
3answers
8k views

Error when trying to run Create Extension postgis

I have installed PostgreSQL 9.x and PostGIS 1.5/2.0 several times and have never had this problem. I just got a new CentOS 6.3 server up and running and and I have Postgres 9.3 functioning as ...
11
votes
1answer
225 views

Naming conventions PostGIS Database

We are starting to build up a database with PostGIS. The database is supposed to be for a team of about 5-8 researches who work frequently with geodata and statistics. Has anybody experience with ...
10
votes
2answers
652 views

What is the precision of SELECT DISTINCT on PostGIS geometry column?

I wonder what the precision of the SELECT DISTINCT operator is on a PostGIS geometry. On my system, the following query gives me a count of 5, which means that the inserted points are considered equal ...
10
votes
1answer
726 views

How to get the length of a line inside a polygon

How can I get the length (brown marked part) of the "Road 7" inside the red polygon? My start: SELECT ST_Length(way) FROM lines WHERE road = "Road 7" AND ST_Intersects(line, polygon)? With that, ...
10
votes
2answers
3k views

Intersecting a raster with a polygon using PostGIS - artefact error

I'm using PostGIS2.0 to do some raster/polygon intersections. I'm having difficulty understanding which operation I should use, and what the quickest way of performing this is. My problem is as ...
9
votes
2answers
4k views

What is the unit used in ST_Distance()?

I wonder what the unit is of the float being returned from ST_Distance. In the documentation it says: ...cartesian minimum distance (based on spatial ref) between two geometries in projected ...
9
votes
1answer
659 views

Are Zonal Statistics possible in PostGIS2?

Now with postgis2 which supports rasters by default is it possible to do a zonal statistics analysis? I've google it but I haven't found anything solid? Is there a tutorial outthere to get me ...
9
votes
1answer
175 views

What PostGIS [topology] provided tools may be used for data quality assurance?

I'm learning about techniques to ensure GIS data quality. Googling around, I've found a few papers about theory and best practices (here and here), and a couple of short presentations about the topic ...
8
votes
2answers
279 views

Moving existing hard drive with PostGIS data directory to a new computer

I have a new computer that is replacing an old machine that has PostGIS installed. The old computer has a 2nd hard drive where the PostGIS data directory is stored. I want to move this drive over to ...
8
votes
1answer
101 views

Building a polygon over a reachable area

I am currently working in the field of isochrones and the underlying algorithms. What now causes problems is not the calculation if the isochrone itself, but the visualization of the results. The ...
7
votes
5answers
2k views

How to split OSM roads into individual segments at intersections?

I want to create a road-network for use with pgRouting using OpenStreetMap data. I loaded a shapefile from GeoFabrik into a Postgres table (with PostGIS enabled). However, one problem I had was that ...
7
votes
2answers
2k views

Postgis 2.0 upgrade problem with postgres data?

I have a serious problem: on Wednesday, April 2 OpenSUSE 12.1 is updated, and the package was to update the PostGIS-a version 1.5.3 to 2.0.0. After this operation, Postgres does not display records. ...
7
votes
3answers
881 views

Snapping start- and end nodes of lines to other lines in PostGIS

There are plenty examples that show how to snap lines to points, but I haven't been able to find any (fast!) way of snapping the start- and end nodes of line strings to nodes of other lines. ...
7
votes
6answers
4k views

Unable to connect Postgis from QGIS 1.8.0

I have installed PostgreSQL and PostGIS on my laptop (Win 7 64 bit OS). I am running QGIS 1.8.0 on my machine . When I am trying to connect Postgis I am getting connection failed error then I tried ...
7
votes
2answers
732 views

How to get distance between one point and all other points in a table?

I have a table in Postgres with lat and long columns. Can I use the ST_Distance_Sphere or ST_Distance_Spheroid methods to get the distance between one point and all other points in the table? Do I ...
7
votes
1answer
629 views

How to set up GeoDjango with PostGIS 2.0?

I would like to run GeoDjango (Django 1.4) with Postgis 2.0. Whenever I try to run syncdb I get the following error for geographic fields: Failed to install index for stores.Store model: operator ...
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
3answers
3k views

Loading geotiffs into PostGIS using raster2pgsql

I have an issue with loading some Ordnance Survey raster data into PostGIS. I have been using the following raster2pgsql command to load a tif that I have placed in my C:\Program ...
7
votes
1answer
49 views

Why is there an upper limit to the SRID value in the spatial_ref_sys table in PostGIS 2.1?

I was trying to insert a reference system in PostGIS from http://spatialreference.org/, and got a constraint violation for spatial_ref_sys_srid_check. I checked that constraint and found that, when ...
6
votes
4answers
1k views

What does the “service” box in the PostGIS connection dialog stand for?

Can sоmеbody explain me what to write in the "service" box when connecting to the local DB. QGIS Lisboa and Postgres are installed.
6
votes
1answer
2k views

How to find the nearest point by using PostGIS function?

table A: lat | long | the_geom | code | sign 13.8433095 | 100.6360357 | 0101000020E61.... | ABC | start_point 13.7544738 | 100.5459646 | 0101000020E6..... | ABC | end_point 13.4124215 | 100.6232332 ...
6
votes
2answers
932 views

Why is any pgr_* routing function taking forever based on OSM data in an pgrouting enabled DB

I loaded the German OSM dataset into the pgrouting DB by using osm2po 4.7.7. Everything works fine i have osm2po set up via it's config and it's working like a charm through it's Java part. I had the ...
6
votes
1answer
4k views

Postgis 2.0 - public.geometry_columns view does not contain correct info for views

We have recently updated our database to Postgis 2.0. In our system we used to create views for tables containing a geometry column. After creating the view we also manually updated the ...
6
votes
1answer
3k views

PostGIS - 1.5 to 2.0 easy update

I have a PostGIS 1.5 database and want to update to 2.0. I read that because this is major release I have to follow the 'Hard update' procedure. However the spatial data I have is only test data so I ...
6
votes
2answers
2k views

PostGIS SQL query to find a polygon that intersects with a line

I need to build a PostGIS query that would select every polygon inside an area the river is flowing through. I'd better describe with an example below: There is a river (blue line) flowing through ...
5
votes
3answers
591 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
2answers
2k views

Force SRID in views in Postgis 2.0

I have a very tricky problem: I have a Postgis 2.0 database, used as datastore for Geoserver 2.1.3 I have a table with a geometry column (named shape), with SRID 4326 I have created a view like ...
5
votes
1answer
855 views

Are there limitations using a PostGIS out-db raster?

I have been trying to import a large raster into a PostGIS 2.0 database and hit a few stumbling blocks. Initially when I tried to import the raster using raster2pgsql, I would receive the following ...
5
votes
1answer
622 views

How to install PostGis on EC2 micro-instance

I tried to install PostGIS on my Amazon EC2 microinstance using sudo yum install postgis2 and got following error message: $ sudo yum install postgis2 Loaded plugins: ...
5
votes
1answer
3k views

Loading a raster using QGIS into a PostGIS2.0 enabled database

Following on from my attempt to install a postgresql 9.1 db with postgis 2.0 on windows 7, I am trying to figure out how to load a raster. I have successfully loaded a shapefile, and am trying to do ...
5
votes
1answer
573 views

POSTGIS SQL - How do I remove (defined) sliver polygons

I would like assistance in writing SQL (for POSTGIS in postgresql) to remove sliver polygons (including long narrow slivers and some small irregular polygons that were erroneous 'gaps' in the original ...
5
votes
1answer
655 views

How to snap line string start & end vertex to the nearest point?

I have a point layer and a line layer. The points are road junctions, collected using DGPS, while the lines are the connecting roads, collected through Handheld GPS in tracking mode. As a result, the ...
5
votes
1answer
595 views

Speed up pgRouting point_to_id using new <-> operator in PostGIS 2.0.0

The point_to_id function, which assigns a node id to a point based on existing nodes and tolerance, is using a query as follows: SELECT id, the_geom FROM vertices_tmp WHERE ...
4
votes
1answer
2k views

Copy a spatial table from one PostGIS database to another PostGIS database

I have two PostGIS databases on two different servers. The first server is PostGIS 2.0 and the second server is PostGIS 1.5. On the PostGIS 2.0 I have a table "lakes" with MULTIPOLYGON geometry ...
4
votes
3answers
108 views

How can I export points as GPX?

I have some point data in PostgreSQL / PostGIS. How do I export the data to a GPX file? I looked for commands similar to ST_AsGML and ST_AsKML but could not find anything to convert to GPX (like a ...
4
votes
3answers
754 views

How to create random points in a polygon in PostGIS

I have a polygon layer, and would like to generate random points in each polygon. An initial searching in Google resulted this website which presents a function for this purpose: ...
4
votes
2answers
1k views

How to render PostGIS raster with Mapnik XML?

I'm trying to manage raster files from PostGIS, which to be rendered using Mapnik. I used raster2pgsql to load those GeoTIFF images to PostGIS, and I think the loading process turned out OK. The ...
4
votes
2answers
2k views

PostGIS - incorrect result distance between two points

I am quite confused the way PostGIS is returning the result for this basic query. So I am trying to find the distance between two points 45.2714,71.2087 and 42.3739,66.39 and the distance should be ...
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
3answers
1k views

How to find lat/lon values for every pixel in a GeoTIFF file?

I want to a store GeoTIFF file pixel by pixel in the database. I need to store pixel values along with the lat/lon of that pixel on the GeoTIFF image. Is it possible to do that?
4
votes
1answer
982 views

PostGIS: Merge multiple table with same GID

I just imported some shapefiles with the PostGIS importer. Because several errors and issues I had to split my shapefile in multiple parts. Then I imported them one by one. I thought I can easily ...
4
votes
1answer
596 views

Transit Routing using pgrouting as core routing engine?

i'm a student and i'm working on a project. the aim is given two sets of coordinates(origin & destination) route through the map to determine the optimum path using public transportation and of ...
4
votes
1answer
241 views

Update PostGIS after updating GDAL?

I am using PostGIS 2.0 and I want to use the raster functionality through mapserver. It seems as if the latest official GDAL release 1.9.1 has an unstable PostGIS raster driver, as pointed out in a ...
4
votes
1answer
408 views

How to Load RDF Data to PostGIS?

I want to load some sample RDF (Relational Data Format) to POSTGIS. Are there any tools or methods to do this.
4
votes
1answer
792 views

remove duplicate points based on a specified distance

i have a table of points with each point acquired on a specified date, by a specified source, and having a measured value. i want to consider points as duplicates if they have the same date and if ...