PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

3
votes
2answers
2k views

How do I get the distance meter value between two geometries in PostGIS?

I have a simple question about calculating distances in PostGIS. I would like to get the distance between two geometries. I am using this sid : 4269 in meters what i am doing now is this : ...
4
votes
1answer
349 views

Find the farthest point (PostGIS)

I have a table with 2000 records and with those columns OutletName, longitude, latitude, geom I would like to find a way to find the farthest outlet between a selection of outlets For example we ...
7
votes
1answer
2k views

Output polygon data from postGIS and read it on Google Maps API

So, I have a group of polygons stored in PostGIS. I'm trying to build a web application with Google Base maps (using Google Maps API). Now, How do i output the polygons in the right format so that ...
5
votes
2answers
848 views

Loading data onto PostGIS using KML files with attibute data

So, I have a kml file which contains 317 data points (317 polygons and the respective 317 names). The kml file also contains attribute data which pops up in a bubble on Google earth with about 12 ...
3
votes
1answer
899 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 ...
3
votes
1answer
565 views

Using Google Earth as view with SQL Server or Postgres Database

How do I connect Google Earth with any relation database? I have live database containing spatial co-ordinates i.e latitude and longitude. User should be able to view these live data on google earth.
2
votes
2answers
2k views

Problem when calculating longitude and latitude for points

I have a PostGIS database with SRID=900913 (Google Mercator) and I would like to know the latitude and longitude of points. I use this command: ST_X(ST_TRANSFORM(the_geom,4283)) Following what is ...
3
votes
1answer
2k views

How to get the coordinate system from a PostGIS database?

My question is probably amazinlgy stupid, but I can't find the answer myself. I would like to extract from a database the latitude and longitude of points. For now, I'm getting x(the_geom) and ...
3
votes
1answer
290 views

Is ST_GeomFromText better than providing direct geometry?

I have been working with postgis recently,and in my query if I use ST_GeomFromText it execute faster than running a sub-query to get geom. I thought ST_GeomFromText will be more expensive but after ...
4
votes
2answers
803 views

Changing Buffer table geometry dynamically in PostGIS using plain SQL

I have two tables in PostGIS, a point and a point_buffer table. The point table has a buffer_distance field, with a default value say 200. Now I want to change the buffer table geometry whenever I ...
1
vote
0answers
247 views

Live KML Feeds - From the Ground Up, How Would You Build It and Why?

I found the "Live KML from PostGIS to Google Earth" post very informative but a bit daunting for someone new to the open source world of products like PostGIS and GeoServer. If I am building the ...
5
votes
3answers
2k views

Can I connect (& edit) to a postgresql database with arcgis without arcSDE?

After trying in vein to get spatialkit and zgis working with my postgresql database in arcgis 9.3 and 10 , I was wondering if anyone has had any luck with these products. My postgresql database has ...
1
vote
2answers
708 views

Projecting OpenStreetMap data in PostGIS

I've loaded an extract of Openstreetmap data into a Postgresql database with PostGIS 1.5 using the following command withosm2pgsql.exe: C:\Program Files (x86)\HOTOSM\bin>osm2pgsql.exe -c -s -H ...
5
votes
1answer
2k views

How do I perform a proximity search with Postgis?

OK, so I downloaded the Geonames database (cities1000) and wrote a small Ruby program to import them into my table (geo_cities). I then added geography column called geog. So after all of that, I ...
0
votes
0answers
92 views

How to pass in a database created on the fly in code to Quick_Export in arcpy?

So I am trying to programmatically use quick_export through the arcpy package in an arcmap tool, and so I referenced the quick_export tool's dialog box that comes up when it is executing for the ...
0
votes
1answer
213 views

Running database operations against a stored GIS geometry

I'm using Quantum GIS and Postgres with the GIS extensions to store shapefiles that I'd like to run point in geometry and point in union geometries against. While it's easy to save and load the vector ...
0
votes
0answers
38 views

Does Shooting star 1.05 not consider reverse_cost? [duplicate]

Possible Duplicate: PGRouting with 1.05 library in Postgres 9.1 Shooting star pgrouting lib 1.05 with postgres 9.1. Dijkstra and A-star working fine but "SHOOTING STAR" not considering ...
14
votes
2answers
2k views

Identifying road intersections using PostGIS

I'm trying to identify where roads intersect each other, and to make a point at this intersection, with the number of roads that form the intersection listed. I was wondering if there was some way ...
4
votes
2answers
839 views

Is Shooting Star broken in PGRouting 1.05?

I'm using pgrouting DLL 1.05 with postgresql 9.1. All required function installed. Astar and Dijkstra work fine with sample data but "SHOOTING STAR" is not considering "reverse_cost". Also if i try ...
4
votes
1answer
185 views

Is it be possible to re-render parts of a tile pyramid when postgis table updates?

I have this idea of using postgis together with a tile renderer for serving my organization with tiled maps. As I believe my data will be partially altered almost on a daily basis it does feel like a ...
7
votes
1answer
1k views

How to perform a spatial join of point and polygon layers in PostGIS?

I have a point dataset representing households that I want to associate with a parcel layer (i.e. assign its parcel_id). With ArcMap, I could spatially join the polygons to the points and specify that ...
3
votes
1answer
770 views

How to use the pgRouting TSP function?

For more than two days I'm trying to make the TSP algorithm work in PostgreSQL/PostGIS. My problem seems to occur when I'm trying to take into account the road network for TSP algorithm. I know that ...
3
votes
3answers
790 views

How to route between lat/lon pairs in pgRouting?

I have set up Postgresql, PostGIS and pgrouting on Ubuntu. I have imported an osm for wales using the following command: ./osm2pgrouting -file wales.osm \ -conf ...
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
215 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
241 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
3k 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
4k 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
708 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, ...
4
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
4k 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) ...
17
votes
4answers
15k 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
556 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
300 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
222 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 ...
11
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
225 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
419 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
127 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
290 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
399 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
348 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
3k 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
979 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
345 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
3k 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 * ...