PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

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 ...
5
votes
1answer
460 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 ...
13
votes
6answers
6k views

How to use ST_Intersection?

Here's a quick summary about what I'm trying to do: I have 3 tables in Postgres, 'a' and 'b', each have a Polygon column, and 'c' has a Point column. What I'm trying to do here is to get the ...
3
votes
2answers
526 views

Updating Apps on OpenGEO suite Community Edition

I would like to update PostgreSQL and PostGIS on Opengeo CE to a newer version, maybe the other apps too (geoserver, openlayers, etc). Can it be done? Can someone point me the right direction? ...
17
votes
3answers
3k views

In PostGIS is it possible to create a View with a unique ID?

When I create a view in PostGIS is there any way to add a unique ID to that view? Just like the "gid" field in any other PostGIS table? Edit: Sorry I should have included this in the original post. I ...
5
votes
1answer
4k views

qgis to local postgis connection - fails

It seems pretty clear I cannot connect to a local postgresql installation out of QGIS. This is my first experience with PostGIS. I am using PostgreSQL 9.0 on a win7 machine. I can see the successful ...
6
votes
1answer
797 views

Intersection of multiple layers in PostGIS

I need to write PL/pgSQL stored procedure for calculation of intersection of multiple layers. For example, for three layers A, B, C this function should return ABC geometry: Function takes as input ...
2
votes
1answer
341 views

How to create source and target ID columns for a linestring table in PostGIS?

I have table paths_3D which has column "the_geom". I need to create a new column source_id which will have starting points from the geom. This statement doesn't work: ALTER TABLE paths_3d ADD COLUMN ...
13
votes
2answers
1k views

Mixing geometry types in one PostGIS table

I've faced with the following problem. I have to migrate from Oracle database to PostgreSQL+PostGIS. Currently all geometries of all types are stored in one table and each record contains "lid" field ...
5
votes
2answers
383 views

Selecting POIs around specific buildings using PostGIS

The problem: I need to select, for each residential building in my table that has say at least 2 pharmacies and 2 education centers within a radius of 1km, all POIs (pharmacies, comercial centres, ...
3
votes
1answer
2k views

Casting a Geography type in PostGIS

I have the following table defined, which uses PostGIS geography. CREATE TABLE test_geog ( id SERIAL PRIMARY KEY, boundary GEOGRAPHY(Polygon) ); I added the following test polygon to the ...
9
votes
1answer
152 views

How to get the contributors on Open Street Map

I have put my osm file in base thanks to osm2pgsql and I am looking a way to get the contributor list. I have searched in my database and haven't found so much... and the osm wiki about the osm2pgsql ...
10
votes
4answers
797 views

Open Street Map and PostgreSQL what do I exactly need?

I make my first steps in the GIS world and everything is very new for me even if I work on a "huge" project (ie: for a single programmer) very related to maps. My project needs to know informations ...
0
votes
2answers
1k views

QGis import partial PostGIS layer using query builder

I'm sorry, but I'm terribly new to the GIS scene (and PostGIS & QGis). I have loaded a whole bunch of OpenStreetMap data into a PostGIS database and successfully drawn a map using Mapnik. Now I ...
3
votes
2answers
472 views

pgRouting: Is it possible to do multiple (concurrent) Dijkstra/A*/A_shooting_star queries at a time?

Disclaimer: the author of the question is a novice in PostgreSQL and pgRouting. As I understand doing a shortest path query in pgRouting is a complicated SELECT underneath. And as I know unlike any ...
4
votes
1answer
325 views

Sort by geometry using INHERITS?

hope everyone's doing well. I have a statewide point data set (parent table: 600K rows, 500 columns) I need to analyze at multiple scales based on several different spatial divisions. I'll also need ...
8
votes
2answers
339 views

PostGIS- GROUP BY for continuous data values?

In the sample code for the PostGIS ST_ConvexHull function, multiple polygons would be created based upon different, discrete values found in the field "disease_type". --Get estimate of infected area ...
5
votes
2answers
1k views

How to get multiple shortest paths with PgRouting in one query?

I want to run shortest path algorithm on multiple source&target pairs at once and get a result as a table and process it then. How do I do this? This query does not work: SELECT a.source, ...
36
votes
7answers
3k views

Best practices for PostGIS production environments?

What PostGIS documentation have you come across that has been helpful when tuning PostGIS for a production environment? I would like to get together with my DBA to setup a Postgresql/PostGIS ...
6
votes
1answer
708 views

Creating columns in PostGIS using osm2pgsql hstore tags

While I feel this may come down to a database question, it feels GIS-related enough that I feel comfortable posting it here. I'll be happy to take it elsewhere if it belongs there, though! I've ...
4
votes
2answers
909 views

Qgis is slow with complex 'postgis query' layer. How to speed up?

I have a postgis layer in Qgis that is a result of a complex query. Every screen update, Qgis runs the Query again and that's slow. Any suggestion on how to speed up? EDIT - Here is the query: ...
6
votes
2answers
4k views

Postgis TopologyException: side location conflict at 226.001 39.5158, what's the problem?

after st_union() PgSQL returns a note: NOTE: TopologyException: side location conflict at 226.001 39.5158 And in the results there are two rows with empty geometry and other rows are ok. What ...
1
vote
0answers
93 views

Getting started with storing LoST and GML searching

I am working on a logging and search database application. The application will receive event records containing XML LoST values (RFC5222 Location-To-Service-Translation). These records will be ...
4
votes
1answer
592 views

Error using ST_AddPoint for adding a new point to a polyline in PostGIS

I have two PostGIS tables, one where I store points, named "nodes" and another with where I store multilinestrings, named "lines". I'm trying to append a new vertex to a line using one of the nodes ...
9
votes
2answers
6k 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 ...
7
votes
3answers
3k views

Technical comparison of Oracle and PostgreSQL

I have searched on google and the archives for a technical comparison of Oracle and PostgreSQL (also MySQL), unsuccessfully. Is there any comparison that covers more than just the basic differences? ...
4
votes
1answer
386 views

Migration from Oracle to PostgreSQL

I emphasise that I am a newbie in databases. Is it possible to migrate from Oracle database to Postgres? If yes do you know any tested solutions?
6
votes
1answer
522 views

Installing pgRouting Ubuntu Lucid

Here are the steps i did: Added ppa to repository sources in synaptic marked all pgrouting for postgres 8.4 apply (everything was ok) restarted postgres But, i just tried a simple function ...
6
votes
1answer
876 views

QGIS WKT Raster Plugin error, what does this mean?

I have been playing with the WKT Raster plugin. I have installed the WKT Raster experimental binaries in PostGIS successfully and followed the instructions in the read me to the letter. When i am in ...
11
votes
5answers
2k views

Speeding up OpenStreetMap PostGIS querying

I have OpenStreetMap data for the Netherlands loaded into a PostGIS database (PostgreSQL 8.3 / PostGIS 1.3.3) using the osmosis schema. This means all tags are stored in a hstore field. In addition to ...
7
votes
2answers
1k views

Basic PostGIS and PGRouting question

I'm using OSX 10.6 and just downloaded and installed PostgreSQL 9.0.2-1 and pgRouting 1.0.5-1 for Postgres 9.0 from here. I'm a confused what my next steps are though. I'd like to try loading a ...
5
votes
2answers
400 views

Trouble with PostGIS and multiple selection sources

I'm attempting to take geometry columns from two different tables, perform basic PostGIS operations on them, and have them returned in a single result set. My SQL statement looks like the following: ...
30
votes
6answers
9k views

How to use PostgreSQL/PostGIS with ArcGIS for Desktop?

Has anyone successfully used PostgreSQL with PostGIS successfully? I am a semi-experienced ArcGIS user and want to build out a database and connect it to ArcCatalog. I've scoured the internet for ...
12
votes
4answers
439 views

What is a suitable Global/Universal Unique Identifier for a PostGIS database?

I have read that using OIDs as a primary key in a postgreSQL/PostGIS db is poor practice because there are instances where these can be reset. Sounds logical, but then what is a suitable alternative? ...
8
votes
2answers
668 views

What are the Pros and Cons of hstore data type in postgis?

PostgreSQL has a module named hstore that implement the use of key/value pairs (it's something like the OpenStreetMap model, but in a single table). What are the Pros and Cons of hstore regarding GIS ...
22
votes
5answers
8k views

Optimizing osm2pgsql imports for OSM data

I'm currently building an instance on EC2 on which to import the entire Planet.osm snapshot of the whole Earth's worth of data for some projects we're working on. I've spun up a large Ubuntu x64 ...
10
votes
2answers
2k views

How do you display related records in QGIS?

Coming from an esri background I have always used relationship classes in a gdb to display records related to a feature. Eg. Relate 1 survey point to many documents, photos etc. Does any one know how ...
5
votes
2answers
954 views

ArcSDE for PostgreSQL installation

I am trying to install ArcSDE 10 for PostgreSQL in CentOS. I tried doing that but am stuck with same errors repeatedly and am thinking of giving one more shot from the scratch. I just want to make ...
6
votes
5answers
1k views

ArcSDE installation with PostgreSQL (error - 1015)

I am trying to install ArcSDE for Postgresql on CentOS 5.5 in i686 architecture. I have successfully installed and configured Postgresql. I did SDE pre-installation and as ArcSDE user, run the ArcSDE ...
12
votes
3answers
921 views

Centralizing shapefile data into database

I've got hundreds of shapefiles from various different GIS projects that I want to start consolidating into a single database platform, currently attempting this with Postgres / PostGIS. Hardly any ...
6
votes
2answers
1k views

Support for PostgreSQL / PostGIS Inherited Tables in MapInfo/QGIS

Does anyone know if MapInfo and QGIS will support inheritance in a PostgreSQL/PostGIS database? I would like to make use of inheritance but need to make sure the software I use will work with them. ...
3
votes
2answers
1k views

Postgres/PostGIS Spatial Query Performance Problem

I have a query in Postgres/PostGIS that I am a bit confused about. The spatial intersection happens very fast, ~ 2 seconds for 10,000, but the update takes > 15 seconds for the same number of points. ...
4
votes
1answer
328 views

Monster Spatial database server suggestions

I'm going to setup a open Mapserver w/ Openlayers, Geojango, Postgis using Postgresql. And I need some suggestions from people that have been shopping for what I need longer than I have. What would ...
5
votes
1answer
297 views

Postgres/postgis cursors

I have a road network with StreetName and min/max address ranges for each segment (ie- StreetName = 'Main St', Min = 100, Max = 199). I also have individual address points with similar information ...
3
votes
1answer
424 views

Is QGIS compatible with PostgreSQL 8.4 Advanced enterprise DB with Oracle compatibility?

Sorry for such a long question. We are trialling EnterpriseDB Postgres Plus Advanced Server 8.4 with Oracle compatibility and PostGIS 1.5.1. I can create a connection to the database in qgis and the ...
4
votes
1answer
388 views

Remove ArcSDE repository and associated files from PostGresql DB

A bit of background first. I have a PostGres DB with PostGIS, I also have ArcSDE installed in this DB and all feature classes created through ArcSDE are using the PG_Geometry. Everything works well! ...
4
votes
3answers
2k views

Arcgis server not showing map service

I have a map service enabled in my server and have kml and wms as well as mapping and feature access capabilities turned on. I can view the wms in qgis, I can veiw the kml in google earth. However ...
7
votes
4answers
2k views

Will PostgreSQL 9 work with ArcGIS 10?

I realize it is not listed as a supported Database. I am interested to see if anyone has tried to install and use it.