Version 2.0 of PostGIS brought a heavy set of new extensions for the PostgreSQL object-relational database, adding greater support for spatial objects.
19
votes
1answer
654 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 ...
16
votes
1answer
153 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 ...
16
votes
1answer
1k 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.refractions.net/docs/Extras.html).
...
11
votes
1answer
158 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
0answers
506 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. ...
9
votes
3answers
4k 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 ...
8
votes
2answers
170 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
202 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, ...
7
votes
2answers
1k views
Postgis 2.0 upgrade problem with postgres data [SOLVED]
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
2answers
162 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
243 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
979 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
225 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 ...
7
votes
2answers
845 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 ...
6
votes
1answer
1k 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
263 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
5answers
732 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 ...
5
votes
1answer
206 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
444 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
4answers
193 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 ...
4
votes
1answer
117 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
598 views
How to design model in Django using GeoDjango?
I am using Postgresql 9.0 and PostGIS 2.0. I am using Django 1.3 and GeoDjango.I have model with id,latitude,longitude and placename. All the places are in USA.
My requirement is following -
User ...
4
votes
1answer
1k 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 ...
3
votes
2answers
257 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 ...
3
votes
2answers
144 views
How to clip from a point layer using postgis?
I've loaded a LIDAR xyz file into a PostGIS2.0 database and created a 3D point geometry column ("tblCLOUD") from the data.
I've also loaded into the database a shapefile that contains six ...
3
votes
3answers
49 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.
...
3
votes
1answer
37 views
How can I check if a multipolygon can be represented as a simple polygon?
I have a large (30m+ records) spatial data set I have imported into a PostGIS database. I did not expect to find multipolygons in the data set, but it does have a few of them. I'd like to take a ...
3
votes
1answer
133 views
Using PostGIS for a large trajectory dataset
I have a spatial trajectory dataset which basically contains about 10E6 (lat,lon,time)-tuples, grouped together by an identifier so they form trajectories.
Currently, SQLite is used to store the raw ...
3
votes
1answer
155 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 ...
3
votes
1answer
148 views
nearest neighbor within table using PostGIS
using PostGIS 2.0 and PostgreSQL 9.1 I have a table of photographic observations, sco_photos. The location column contains a PostGIS geometry point specifying where the observation was made. The table ...
3
votes
1answer
377 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 ...
3
votes
1answer
41 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 ...
3
votes
1answer
460 views
Postgres/Postgis doesn't work after update
I work on OpenSUSE 12.1 and after today's upgrade (2012 NOV 14) Postgres and Postgis doesn't work. If I login to Postgres server by PgAdmin, get the message: translated from polish
"ERROR: can't acces ...
3
votes
1answer
857 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 ...
3
votes
2answers
725 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 ...
3
votes
1answer
164 views
How can I find the nearest points (plural) on a line segment using PostGIS
I'm locating the nearest point on a line segment with PostGIS and ST_ClosestPoint, I can select the point on each of these line segments which is closest to the starting point. Which looks something ...
3
votes
2answers
458 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 ...
3
votes
3answers
114 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?
3
votes
1answer
213 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 ...
3
votes
1answer
119 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.
3
votes
1answer
336 views
PostGIS 2.0 ST_Transform with Rasters cannot find custom SRID
I'm trying to calculate an average value in a buffer around a point.
Given a raster in EPSG 4326 and a similar point, this is fine, except that the values will be skewed as they are not projected in ...
3
votes
0answers
56 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: ...
3
votes
0answers
66 views
PostGIS 2.0 st_geomfromgml using LineString fails with invalid GML representation
I am attempting to insert a geometry into a table using the st_geomfromgml function.
The GML is being sourced from a Geoserver WFS request.
The function call looks like this
...
3
votes
1answer
190 views
gdalinfo gives no georeferencing information for a PostGIS raster
I have a PostGIS raster dataset which I am trying to access via GDAL. I've had various problems with making this work, so thought I'd try running the gdalinfo command to check that GDAL can actually ...
2
votes
1answer
82 views
How to Find the Nearest Node from a Given Coordinate
I have already developed a table manually. It contains the coordinates and the distance of all the nodes, there is no geom field or any field related to postgis. But I want to find out the nearest ...
2
votes
2answers
184 views
What's the fastest way to search for polygons that intersect with others?
I'm querying the database for searching polygons that intersect with others, but is very slow research, wanted to increase the speed.
I thought of using sphinx, solr, elastic search googled but did ...
2
votes
1answer
312 views
Access PostGIS Raster layer through mapserver
I am trying to use a cgi script calling mapserver to display a raster layer that is stored in a PostGIS database. However, only a blank white image is returned and I don't know where the problem is.
...
2
votes
1answer
265 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
...
2
votes
1answer
541 views
pgRouting: unable to execute routing_core.sql
I'm unable to execute the routing_core.sql, receiving following error.
I'm using pgAdmin III, Windows 7, PostgreSQL 9.1, PostGIS 2.0.
ERROR: could not load library "C:/Program ...
2
votes
1answer
61 views
Calculating points in the USDA's Cropland Data Layer coordinate system
I need to integrate against the USDA's cropland data layer. They've got a demo and some API docs.
The important query I need to run is this, given an (x, y) of say (1551459.363, 1909201.537):
...
