PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

2
votes
1answer
186 views

How to create infrastructure coverage using pgRouting?

I'm trying to reproduce what Underdark did here. I succesfully installed pgrouting and the two required function (nearest neighbour and driving_distance). cdv_osm_v contains all the openstreetmap ...
1
vote
1answer
154 views

Intersecting 3 tables with Spatial SQL

I have three tables I need to intersect but not sure how to get the third Heres what I have so far: SELECT nyc_neighborhoods.name, SUM(nyc_census_blocks.popn_total) AS POP FROM nyc_neighborhoods JOIN ...
5
votes
3answers
466 views

how to parse a XML Document file associated with the shapefile into a PostGIS/PostgreSQL table

I have a shapefile, and information on its attributes is stored in a XML Document format. For example, the attribute of COUNTY is stored as follows: -<attr> ...
1
vote
2answers
2k views

How to find nearest line from point using PostGIS?

I have a table of Roads in PostGIS which geometry is "geometry(MultiLineString,4326)". Its attribute are gid osm_id name ref type oneway bridge maxspeed geom Now i want to know ...
2
votes
1answer
167 views

How to link pgrouting edges with street names to route user based in street names?

I have installed postgreSql + postGis + pgrouting and I have loaded OSM data into my database using osm2po and shp2pgsql. How can I search for points in the map using the street names and then route ...
4
votes
1answer
360 views

Is there a way to select what to import from planet-latest.osm.bz2 to database?

Is there a way to select what to import from planet-latest.osm.bz2 to database? I want to do something like : osm2pgsql ... --place='city' --admin_level='2' My goal is to use only cities and ...
1
vote
2answers
273 views

How to remove shapefile from PostGIS

I added a shapefile to the PostGIS using, ogr2ogr -f PostgreSQL PG:"host=server_ip user=username dbname=dbname password=password" yourshapefile.shp; Now I want to remove it. What is the command ...
10
votes
2answers
9k views

Adding shapefiles to PostGIS database

I was able to create a Postgresql database and then I executed, createlang plpgsql geospecies psql -d geospecies -f postgis.sql psql -d geospecies -f spatial_ref_sys.sql So now can I directly ...
0
votes
1answer
545 views

Unable to locate PostGIS in OpenGeo Suite using command line

I am working on this new ubuntu 12.04 server and I need to work on the Postgres database which is PostGIS enabled. The problem is I am not sure how to locate the database using command line. The ...
3
votes
2answers
161 views

Multiple queries in a row

I have osm data imported into PostgreSQL with osm2po tool (wich makes the data routable). Also I have imported data relationed with nodes and node_tags with osmosis tool. I need to obtain a set of ...
0
votes
1answer
849 views

Django client install postgresql/postgis on Ubuntu 12.04

Does anyone know what apt packages one should install for a postgres(8.4)/postgis(1.5) django (1.4) client on ubuntu 12.04? All recipes seem to assume that I want to install the full ...
2
votes
1answer
549 views

QGIS ->ArcGIS Server configuration possible?

Is it possible to connect a QGIS Desktop 1.8.0 to an ArcGIS Server 10.0 ? and of course publish services on it?. The configuration would be like this Quantum GIS-> ArcGIS Server 10.0 on Amazon EC2+ ...
4
votes
3answers
533 views

How to find out what features /tags are included in an .osm or .pbf file?

I would like to load osm data into a postgis (postgresql) database. In How to load OSM data into PostGIS preserving all Tags? there has been a solution posted: Download the OSM files (in .osm ...
3
votes
0answers
282 views

Is there a way to start the route on pgrouting from a coordinate that is not a node?

After execute all steps from this pgRouting tutorial, now I have a map that creates and displays routes, but as the routing methods rely on parameters representing vertex Ids that was created using ...
6
votes
1answer
476 views

Join based on maximum overlap in PostGIS/PostGresQL?

I have two sets of polygons in two tables. The sets overlap each other. For each polygon in set A, I would like to get the ID of the polygon in set B that it overlaps the most. I'm using PostgreSQL ...
2
votes
1answer
245 views

Creating 3D terrain from point data in PostgreSQL?

I am quite new to arcgis and I would like to create a 3d terrain. Now I have a postgresSQL table containing a list of points with their geometry data and the Z value for each point. What I would ...
5
votes
3answers
3k views

How to find the nearest point from POI in PostGIS?

I have Point of Interest data in MySQL. This are the fields latitude,longitude,address,types in which I have stored the data. Now i want to import it into PostGIS Database. But does it required ...
8
votes
2answers
563 views

PostGIS Intermittent INDEX Performance

I have a table containing approximately 55 million data points ( point is a geometry with SRID 4326 ) and for my query I need to join this to an area table ( currently have ~1800 areas ) which ...
7
votes
2answers
323 views

Update database row with points that fall within polygons

All, I have a PostGIS/Postgresql DB that has two tables in it. One with point geometries and the other that represents country boundaries as polygons. I would like to add the country name that each ...
4
votes
1answer
351 views

Improve database performance for geospatial

I have a database with osm data imported with osmosis tool. I want to search for the nearest POIs from a given point. For this purpose, I perform this query: select id from nodes where ...
3
votes
1answer
1k 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 ...
2
votes
1answer
305 views

update tables data dynamically

Can u help me in creating a trigger and function so that every time i change the flag in table A the buffer distance should get changed and also its geometry field also , ie the polygon area? I have ...
5
votes
2answers
609 views

OGR to read from oracle, and then write a CSV

I need to read an Oracle (not spatial) table with OGR. I have no problem with ogrinfo. I use this command: ogrinfo -ro OCI:user/password -sql "select * from HR.EMPLOYEES" I have this kind of ...
3
votes
2answers
641 views

Why does Ogr2Ogr Postgres import fail?

I have a lot of S57 files that need to be imported to PostgresDB. The process takes over 2 hours. In order to improve performance, I have tried using PG_USE_COPY to use copy instead of DB inserts. I ...
3
votes
1answer
682 views

ArcGIS Server 10.1 and Postgres Geoprocessing Service issue

I'm hitting an error when publishing a model from ArcGIS Desktop 10.1 to ArcGIS Server 10.1. The model I want to publish uses a layer from a Postgres DB defined via a Query Layer in my MXD. The ...
6
votes
1answer
112 views

PostGresql and PostGIS courses in Canada

I have been looking for good professional courses for learning more than just the basics for PostGresql nd PostGIS. I have found a number of good courses, but they seem to all be in the US. I was ...
5
votes
2answers
6k views

How to connect to Postgres with QGIS?

Oddly, it should be simple, but I can't connect to a local PostGIS database. My installation is with Mac OS X (10.8), Postgres.app (in doc it's said to have PostGIS included) and Quantum GIS 1.8. ...
4
votes
1answer
284 views

extracting pixel value from a table in postgresql

I have a table in postgresql that contains ID, row number, column number, pixel value and centroid of the pixel in WGS84 lat-lon system(4326). How could I extract the pixel value by giving a point in ...
6
votes
0answers
680 views

Getting ST_Difference between two different tables in postgis [closed]

One table has a series of grid squares in it, and the other has millions of voronoi polygons in it. I need to create a table that has the difference between the two of the them so that I effectively ...
2
votes
2answers
1k views

How to import shp files into postgresql with PostGIS?

I'm a new user of postgreSQL/postgis but I'm having a problem. I'm trying to import some shapefiles (which open perfectly in QGIS or ArcGIS but create an error when i import them). The problem is not ...
2
votes
2answers
770 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 ...
3
votes
1answer
819 views

raster2pgsql: Strange error when creating a table

Hi GIS Stackexchange people, When I try to load a asc file into my psql setup, I get a strange error message. I have created the sql input file via raster2pgsql -s 4236 -I -C -M gis_data.asc -F ...
2
votes
0answers
277 views

Postgresql Query Plugins Not Returning Results in QGIS

I'm using the Fishnet function as described here to create a grid. If I use the following SQL to call the function to position a grid where I want it (I'm using NZGD2000 SRID = 2193) the query ...
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. ...
16
votes
1answer
187 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 ...
1
vote
0answers
93 views

Calculating spaces between roads from PostGIS database

Is it possible to calculate the spaces between the roads using a select statement in a PostGIS/PostGre Database? I tried using some combinations of the predefined functions as ST_Intersects & ...
6
votes
3answers
718 views

postgis : is the operator <-> works for Geography also?

With recent postgres//postgis version, you can now use the operator <-> to find nearest rows to a point : Indexed Nearest Neighbour Search in PostGIS Correct me if I'm wrong, but according ...
5
votes
1answer
398 views

Parse error while inserting values into polygon feature

I created a polygon as follows create table boundary (id int, name text); table created; select AddGeometryColumn ('',boundary', 'latlon' 4326,'POLYGON', 2); geometry column added; then i'm ...
3
votes
2answers
1k views

raster2pgsql part of PostGIS?

Hi GIS Stackoverflow people, I wanted to use the PostGIS functionality raster2pgsql, but I had to discover that the Python function was not installed with my PostGIS installation. Do you know if ...
2
votes
1answer
418 views

How to install PostGIS 1.5 on Mac OS 10.5?

I am running PostgreSQL 9.0 on Mac OS 10.5. I've installed postgis-pg90-1.5.3-1-osx through "Stack Builder" but still don't see postgis in my list of databases when I open PgAdmin. When I open my Qgis ...
9
votes
2answers
473 views

Best design for Open Source Python / PostGIS prototype

I am writing a data intensive web application that is delivered through apache. My question is about how to best arrange processing given that there are multiple options. I have at my disposal ...
4
votes
2answers
3k views

How to select segment of linestring using PostGIS?

i have PostgreSQL+PostGIS. I have a linestring: "LINESTRING(60.7014631515719 56.8441322356241,60.7023117507097 56.8445673405349,60.702948200063 56.8447993944193,60.703902874093 ...
0
votes
1answer
275 views

What is the fastest way to identify the two nearest linestrings using PostGIS?

I want to be able to identify the closest roads to two points that are generated through geocoding (a start and end address). The points will likely be near a linestring. Two approaches that I have ...
1
vote
0answers
209 views

How to view error feedback in qgis when testing connection to postgres database?

Using qgis 1.8 on Ubuntu 10.04 with Postgres 8.4.13 I am creating a new (and my first) Postgresql connection in qgis. I can connect to the user and the db using the pgsql client. ...
1
vote
0answers
2k views

How to populate data from postgresql using dynamic php\html listing

In the form I created, I need two fields to be dynamically populated from the database while the other fields will be manually entered and push to the database. I have written a script that is able ...
2
votes
1answer
161 views

How to create a network with all the track points

I have created a sample network through the use of gps visualizer. Than I used the ogr2ogr function to import this data in my database. Now I have a table which holds all the track points of these ...
5
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 ...
9
votes
1answer
604 views

How to view a 155 MB raster stored in PostGIS?

I am using the following command to load a raster into a postgres database: raster2pgsql -I -C -e -Y -F -s 3086 -I -C -M myraster.tif myraster -F -t 30x30 | psql -U postgres -d database -h localhost ...
2
votes
1answer
171 views

Finding the Geometry within which a Point falls

I have the following query, which takes on average about 4 seconds. How might one improve the query such that the same response comes back faster? SELECT * FROM geometries g ORDER BY ...
6
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 ...