PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

4
votes
3answers
1k views

How to make pgRouting faster?

I have table network which has about 2 million records (only 3 countries in Europe). When I run Shorthest-path it is very slow, it takes 3 min to take records only. I tried with ...
2
votes
1answer
222 views

PostgreSQL / PgRouting syntax error

My question is about Postgres/PgRouting syntax, when I wrote query like this: SELECT * FROM shortest_path('SELECT gid AS id, source::int4 AS source, target::int4 AS target, ...
4
votes
1answer
254 views

Spatial index vs two coordinates indexes

I have a table with two columns: latitude and longitude. I want to get all objects inside a "rectangular" (well, rectangular in a lat/lon coordinate metric) bounding box: min-max latitude and min-max ...
2
votes
5answers
4k views

What causes this unexpectedly poor GeoServer/PostGIS/OpenLayers performance?

I am having a tough time making this map run smoothly (play around with it for a bit, you'll see). I have taken a few tips from OSGeo's Making Maps Fast document, but I think there's another issue ...
1
vote
2answers
5k views

How to install PostGIS shp2pgsql-gui on CentOS 6?

I am installing PostGIS 1.5.3 on a CentOS 6.2 virtual machine to get started web mapping with Geoserver.I have successfully installed PostgreSQL 9.1, PostGIS 1.5.3 and PgAdminIII on the system. ...
3
votes
2answers
727 views

How to use pgRouting's dijkstra_sp_delta_directed with WHERE clause?

Right now I am using PgRouting for vehicle routing and I have problem with Dijkstra shortest path. My query is: SELECT * FROM shortest_path('SELECT gid AS id, source::int4 AS source, ...
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
5k views

Simple PostGIS ST_Distance query for two points from the same geometry table?

Note: This question was published at Stackoverflow at first. I have a geometry table points_table with SRID 4326 and a few points in it. I need to find a distance between a pair of points (features) ...
20
votes
4answers
16k views

Why does PostGIS installation not create a template_postgis

I went this route for installation of PostgreSQL and PostGIS: PostgreSQL using the standalone KyngChaos, PostGIS install from KyngChaos as well... pgAdmin3 from PostgreSQL Tools Now when I launch ...
5
votes
2answers
561 views

Accessing non-spatial PostgreSQL tables using OGR Python bindings

When connecting to PostgreSQL/PostGIS using the OGR Python bindings is it possible to get non-spatial table names (i.e. OGR layers) from the connection? Currently, doing: conn = ogr.Open("PG: ...
2
votes
1answer
302 views

How to import selected features from QGIS to PostGIS?

I am trying to import selected features from Quantum GIS to postgresql directly. I do not know how to do it. Actually, my problem is, I have full country road network. But, I need to cut road network ...
2
votes
0answers
224 views

OSM Database created: now what happens?

I imported an OSM file into a Postgres DB using the Osmosis tool. Now, I need to extract some data but I can't find any schema description (I'm really a newbie in GIS). Where can I find it? Do you ...
12
votes
4answers
7k 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 ...
2
votes
2answers
228 views

How to remove roads from dataset

I've got a shape file that I loaded into PostGIS that has both road and parcel multipolygons and I need to remove the roads. I tried loading the shp file in qgis and it loads them both as a single ...
4
votes
1answer
2k 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 ...
0
votes
1answer
424 views

Unable to Make PostGIS 2.0 - file not found

I am struggling to get the latest trunk of PostGIS to install on a fresh Mac running Lion. I did install PostGIS 1.5.3-2 for Postgres 9.1 from [KyngChaos Mac OSX Installers][1]. This setup worked ...
1
vote
1answer
128 views

How Do I Debug QuickExport Parameters in the Data Interoperability Extension for ArcGIS 10 to Exporting to Postgres

I tried to configure the QuickExport parameters by examining the log file after I used the QuickExport tool itself. The only things I changed from the log file are the parameters as I want the user to ...
7
votes
2answers
3k views

Can't import OSM data to PostGIS, permission denied

I use Postgres 9.1 on Xubuntu 11.10. I've created a db 'aus' and user 'aus', granted ALL on the db to the user. Now I try to import the data, and it outputs this error: $ osm2pgsql --create ...
4
votes
1answer
294 views

generate 1-18 simple worldmap with mapnik

I have postgresql with postgis and mapnik, I need to generate a tiled world map with only country contours and country name that are displayed at all zoom levels, no extra info. How can I do it ...
5
votes
1answer
414 views

Efficiently querying a region-partitioned PostGIS table

I have two PostGIS tables: one of streets, and one of regions, which, apart from spanning the same spatial extent, are a priori unrelated. The streets queries I perform can be restricted to a given ...
4
votes
2answers
356 views

Why is osm2pgrouting not importing?

I installed pgrouting successfully and did a little import with a 20Mb file. Works good! Now I'm trying to import a file with the size of a country and it doesn't even start to import. The last 2 ...
5
votes
1answer
4k views

.NET Entity Framework with PostGIS?

Is it possible to use the .NET Entity Framework with PostGIS? I've reviewed a few options, including hookup it up with Npgsql using ...
6
votes
2answers
1k views

Creating a spatial PostGIS table as a result of a PostGIS query

When I want to store the results of a spatial query as another PostGIS table, what should I do? I am interested in identifying all residential buildings that fall within a set of parcels: CREATE ...
6
votes
2answers
351 views

Memory considerations for cloud based GeoStack

Some time ago I asked about hardware considerations of cloud based GeoStack implementation. As a follow up to this question There are some good pointers in related question already, but they mainly ...
15
votes
3answers
3k views

How can I optimize pgrouting for speed?

I am using pgrouting on a postgis database created through osm2pgrouting. It performs very good on a limited dataset (3.5k ways, all shortest path A* searches < 20 ms). However since I have ...
3
votes
2answers
3k views

Import Shapefile to Postgres

I am trying to import a shapefile to Postgres using the following: shp2pgsql c:\location\water_areas.shp water > water.sql I am receiving the following message: c:\location\water_areas.shp: ...
6
votes
1answer
4k views

How to import a raster into PostGIS?

I'm trying to follow the instructions here to load a raster into a PostGIS database: python raster2pgsql.py -s 4269 -I -r *.tif -F myschema.demelevation -o elev.sql I understand that I replace * ...
13
votes
6answers
11k views

How can I enable Shapefile GUI Loader in pgadmin3?

I have installed PostgreSQL 9.1 with PostGIS 1.5.3 on Ubuntu 11.10. I also installed pgadmin3 1.14.0-beta1, following this instructions. If I remember correctly, when I installed PostGIS on Windows ...
4
votes
1answer
337 views

DBMS and ArcSDE Architecture for Large (Mostly Non-Spatial) Dataset

I have ~1000 fixed points representing data collection stations with multiple sensors, and a csv that is ~4.5GB of values in the format: id pointid sensor measdatetime measvalue 50345 500 ...
1
vote
1answer
986 views

Mapserver/PostGIS Query Error

I'm having some difficulty understanding the error I am getting from my mapserver/postgis program. I have the following map file: MAP # start of map file NAME "sample" EXTENT ...
2
votes
1answer
2k views

Basic MapServer query of PostgreSQL/PostGIS database

I've just installed MapServer on a linux server and I am trying to understand how to display a basic map using a PostGIS database. I have displayed a shapefile in the browser, so I know that the ...
2
votes
1answer
351 views

Error while importing shapefile using shp2pgsql: Coordinate values are out of range

I have a shapefile with 180 points. I'm using shp2pgsql-gui via pgAdminIII. This is an error it gives me when I try to import shapefile. Failed in pgui_exec(): ERROR: Coordinate values are out of ...
11
votes
1answer
562 views

Nearest Neighbor Grouped Selection in QGIS

OK - so I have an interesting problem I am trying to solve. I have a list containing over 100,000 points in lat/long format which I have imported into qgis. Now, what I am trying to do here is group ...
5
votes
2answers
459 views

PostGIS: Approximating a house number from address range

I'm developing a reverse-geocoder for Canada. So far, given a lat/lng, I can find the nearest street (line segment), which includes line segment direction and address ranges for both sides of the ...
6
votes
4answers
1k views

Rename postGIS table and updating the GeoServer?

I want to rename one of my postGIS tables. However, when I do this by applying a simple ALTER TABLE old_name RENAME TO new_name; The spatial data, as I understand it, isn't updated with the new ...
5
votes
2answers
398 views

ArcMap Draw Camera Range with ArcPy

i am working on some camera data. I have some points which consist of azimuth, angle, distance, and of course coordinate field attributes. In arcmap I want to draw shapes like this: The shape is ...
6
votes
1answer
1k views

PostGIS and invalid SRIDs

I'm having difficulty with a PostGIS query. I am trying to query my database (world_countries) using a bounding box drawn on a google web map. The objective of this query is to find any countries that ...
5
votes
3answers
1k views

Developing Web Application For Map

I have huge data of maps usually most of the table are co-ordinates and the other are raster maps stored in SQL server 2008 and PostGres Server. I am using desktop application to analyse the data. ...
2
votes
1answer
259 views

How to modify area polygon in postgres? [duplicate]

Possible Duplicate: Automated correction of parcel geometry based on area measure I need a function to modify area polygon in postgres the major probleme is automated correction of parcel ...
5
votes
2answers
427 views

PostGIS ST_Distance vs. pgRouting

I am looking to create a city directory web app, and the site will need to be able to calculate distances between two geospatial points (like the distance between the user and a restaurant, or the ...
10
votes
2answers
4k views

Finding the closest Geometry in PostGIS

I've been looking over the PostGIS "API" of functions, and I notice that most of them take two elements to compare. For example, the ST_Distance function takes two geometry/geography elements to find ...
3
votes
4answers
2k views

Migration from postgresql 8.4 to postgresql 9.0, postgis 1.5 involved

Im migrating from 8.4 to 9.0, in pg8.4 i have postgis 1.5.1 and in 9.0 im just installed 1.5.2. I decided to dump the postgis databases before the pg_upgrade command, and delete them, planning to ...
4
votes
1answer
3k views

Why is my PostGIS query not using a spatial index?

I am having an issue with a spatial query that is not making use of indexes. My schema is: CREATE TABLE geoplanet_place ( woeid integer NOT NULL, "name" character varying(300) NOT NULL, ...
6
votes
3answers
2k views

How to load OSM data into PostGIS preserving all Tags?

I am new to the field of GIS and am trying to view source tags in OSM. I have downloaded the shapefiles from http://downloads.cloudmade.com and have loaded them into Postgres. When I view the data, I ...
2
votes
2answers
238 views

File paths in a PostGIS Table in QGIS

I have a PostGIS table which I have stored the location of some viewpoint photographs. I would like to store the location of the photo file in a field, but when I add a file path in the attribute ...
6
votes
2answers
629 views

How to get an array of simple lines from one multipoint linestring in PostGIS?

How can I extract some LINESTRING consisting of 3 or more POINTs to several LINESTRINGs each of 2 POINTs in PostGIS?
5
votes
2answers
2k views

GDAL Python bindings not picking up OGR PostgreSQL driver

I have a windows server (Microsoft Windows Server 2003) with: Python 2.7, PostgreSQL 9.04-1, GDAL 1.8.1 (installed from the windows installer (gdal-18-1500-core.msi) hosted at ...
4
votes
2answers
416 views

Does PostGIS not work with psql?

I have a sql script that creates several tables, a trigger, and a trigger function. All the commands work in pgAdmin when I execute them in the "Query" section. My goal is to put all these commands ...
7
votes
4answers
4k views

How to fix “Access to database denied” when accessing PostgreSQL?

I got a tiny problem regarding connection to my server. What should we do when this message appears in front of you? I've done several changes in the *pg_hba.conf* entry but yet to succeed. Any ...
3
votes
1answer
2k views

PostGIS Error: type “geography” does not exist

I just upgraded my development machine to Windows 7 and am therefore reinstalling PostgreSQL and PostGIS. I used the Postgres Plus installer, as recommended on the Postgres website, and followed this ...