PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

4
votes
1answer
311 views

postgis create geom from lat long

I ran this query successfully, but the column highwayid isn't updated as expected: update unique_latlongs set highwayid = (SELECT h.osmid FROM rn.highways h WHERE h.geom && ...
-2
votes
1answer
293 views

How do I set the protocolVersion using the jdbc postgres driver? [closed]

I attempted to concatenate a string to the url parameter of the below in order to set the protocolVersion, but I'm not sure how to keep the string in the url without altering the name of the database, ...
2
votes
3answers
364 views

how can i use the postgresql's Geometric Types

How can i implement Geometric Types in postgresql db.. Postgresql db is hosted in windows os.. and how can i get whether point is in circle or not? getting errors when i am using predefined ...
0
votes
2answers
2k views

slow import via osm2pgsql to postgresql database

I try to import berlin.osm (33Mb) into the postgres database 9.1 via osm2pgsql. My desktop is mem 2Gb,proc 2Ghz,hdd 60Gb, RHEL6u2x64. This process is so slowly in particular when I'm going over ...
7
votes
2answers
861 views

vehicle tracking system in postgresql performance

I am developing the vehicle tracking system using postgresql. When the data comes from device to my DB server, I am calling the following functions after inserting data into log table. Distance ...
10
votes
2answers
2k views

Postgres/Postgis spatial index - no speed up

I have a spatial table in a postgres/postgis database. Every row in it represents a Polygon. It is of following form: +----+--------+ |gid | way | +----+--------+ |241 | 01030..| The geometric ...
1
vote
1answer
446 views

Broken image from mapserver, png not rendering

I'm trying to render WMS MapServer data on top of Google Maps using the OpenLayers JS library. My problem arises when I try to render the data as a series of points throughout the New York City area. ...
0
votes
1answer
282 views

Column exists in DatabaseMetaData, but does not exist in relation?

I am using JDBC to query a PostgreSQL database. And I want to populate a column called wiki_url based on the value of another column, level which will determine which value of 5 columns to concatenate ...
2
votes
2answers
859 views

How to project a geoJSON file in OpenLayers?

I have a json file well formed. I want to read a json file as a base layer from OpenLayers. <script type="text/javascript"> var styles = new OpenLayers.StyleMap({ ...
3
votes
3answers
299 views

How to create feature dataset by pgAdmin(postgres) that can by use in sde conection?

How to create feature dateset in postgres(pgAdmin) that can by use in sde conection (arcgis)?
1
vote
0answers
180 views

Convert native box to geometry

I have an old table that contains bounding boxes of lat long coordinates in a "box" type. How do I convert them to PostGIS geometry ? I tried using ...
3
votes
1answer
1k views

Merging multipolygon data into a polygon

I have a MultiPolygon representation of some shapes in POSTGIS, I am trying to convert them to Polygon data. I tried, st_geometryn(r.geom, 1) but that didnt work because the way polygons are ...
0
votes
1answer
402 views

Eclipse can't find Driver.class from postgresql-9.2-1002.jdbc4.jar file nor AbstractJdbc4Connection class

I am using the jar file in my configuration path after following the example from postgresql/jdbc tutorial. But after I added the jar file and configured it for my reference library when I run it I am ...
2
votes
0answers
183 views

Projecting PostGIS in Openlayers

I have a layer(base layer) with MultiLineString Geometries and now I need to project the layers(overlays) in the base layer from PostGIS using OpenLayers. However, I got an idea how to project table ...
2
votes
1answer
238 views

How to filter or query data in GRASS?

I have a PostGIS dataset with just over 2 million rows. Each row has a PostGIS geometry point value and several attributes. I'd like to do some analysis on certain subsets of the data, but I can't ...
6
votes
2answers
2k views

PostGIS ERROR: could not load library “/usr/lib/postgresql91/lib64/postgis-2.0.so”

OS is openSUSE 12.1, PostgreSQL - 9.1, PostGIS - 2.0. Postgre/PostGIS are installed from Application:Geo repository. After recent updates when I try to connect to my database I get the following ...
6
votes
3answers
226 views

Are there any open source Python modules compatible with arcpy that will allow complex SQL queries?

I am trying to handle string (non-geometry) duplicates in a large attribute table of a shapefile without having to convert to gdb and back or connect to a postgres db. I need to be able to use a count ...
3
votes
1answer
580 views

How to manage the osmosis command line?

This refer to this post : Read Here I'm in trouble when using osmosis command line to export data from planet-latest.osm.bz2 directly to osm2pgsql using some criteria to select what to transfer. I ...
1
vote
0answers
366 views

How do I find the pgAdmin connection parameters?

I need to create a connection from arcmap to pgadmin using a python module because of an arcpy script, but I am unsure of how to get the pgadmin connection parameters. I think it may be some kind of ...
3
votes
1answer
156 views

Creating a vector based design from a raster database

I have a raster table that I've created using Postgis raster. It has 365 records and the table looks like this: +-----+------------+---------+------------+ | rid | rast |filename |date_of data| ...
1
vote
1answer
1k views

SRID 4326 and geometry conversion

From this line: SELECT ST_AsEWKT(geom) FROM buildings LIMIT 1; I get this result: "SRID=4326;MULTIPOLYGON(((404269.308014269 4543087.6184561,...)))" I'm wondering if the coordinations are ...
2
votes
1answer
2k views

How to fix incorrect SRID of the geometry column? [duplicate]

I've been told to use AddGeometryColumn to properly add a column with correct/preferred SRID. however I wonder if it's possible to fix/change the current column instead of adding a new? If there's no ...
0
votes
1answer
167 views

Why does Find_SRID return zero?

using SELECT Find_SRID('public', 'myTable', 'myColumn'); I get zero as the result, what does it mean? Regards,
1
vote
1answer
265 views

loading multiple shapefiles using shp2pgsql in a bash script

I have this bash script (loadShapefiles.sh) to load multiple shapefiles at a time. The script and the shapefiles are stored in the same directory. When I run the bash file with the command of bash ...
0
votes
0answers
148 views

Can I do full SQL expressions through the “WHERE” clause in UpdateCursor?

Run as a standalone python script for a shapefile in a toolbox on ArcMap. For example: psql_url_pop = '''SELECT shapefile.NAME_# + shapefile.WIKI_URL FROM public.shapefile''' rows = ...
1
vote
2answers
644 views

dealing with 2 different coordination systems in postGIS

I'm seeking a function that converts degrees to the following format: POINT(405115.926025391 4543389.4354248) I dont know this format's name, but is there any function to do so? Cheers, EDIT ...
2
votes
1answer
593 views

Getting points within a range from a postgres database?

ok so this is query i'm making which returns no result: SELECT building.gid FROM building WHERE ST_Dwithin(building.geom, ST_GeomFromText('POINT(long lat)',4326), 400) If I run this ...
2
votes
2answers
1k views

Spatial index to speed up the query

I have spatial table in PostgreSQL 9.2 / postgis 2.0 that I have to extract some information from it. But my query is hopelessly slow. How could I make use of spatial index to improve the performance ...
3
votes
4answers
1k views

What is the easiest way to detect geometry type from text in PostGIS?

I have some records with different types of 'geometries (poly,line,points)' stored as string. I'm trying to use ST_GeomFromText as I want to do some intersection with them. What is the easiest way to ...
0
votes
0answers
18 views

Is it possible to query for values in fields with the column names partially matching another field's values in psql?

I am trying to query the values of a series of columns NAME_# with the # matching the values of another column LEVEL and then populating another column with what I retrieve
5
votes
2answers
146 views

Confused about PostGIS and Postgres terminology

I am documentating my stuff and not sure what is the right way to write this, "I have installed Postgres and added the shapefile to the Postgres database. This database is then PostGIS enabled" or ...
3
votes
2answers
6k views

How to install PostgreSQL 9.2 with PostGIS 2.0 on Ubuntu 11.10 (or higher)?

I have read this question: Install PostGIS v2 for PostgreSQL v9.2, but it didn't resolve my problem. I added the PostgreSQL backports PPA and installed the postgresql-9.2 package, but there is no ...
2
votes
1answer
152 views

Why is pgrouting astar not consistent?

I route from 1 end of a circular route to the opposite end. Visually it is obvious that going clockwise is shorter, however shortest_path_astar sometimes picks clockwise and sometimes anti-clockwise. ...
2
votes
0answers
11 views

updatation query along a point [duplicate]

Possible Duplicate: GIS QUERY on point and buffer All, I have a PostGIS/Postgresql DB that has one table in it. One with point geometries and the other that represents buffer as polygons. ...
2
votes
1answer
807 views

How can I fix QGIS PostGIS Manager error 'shp2pgsql' is not recognized?

I have QGIS 1.8.0 installed via OSGeo Installer and work on win 7. All I need is to upload the shapefile to the database via this plugin. Unfortunately I am receiving an error: Something's wrong: ...
4
votes
3answers
99 views

GIS QUERY on point and buffer

I have a point table and the buffer geometry is created around the point. I have defined the if the point is within the buffer_distance then its value is =1 and if it is outside the buffer its value ...
2
votes
1answer
236 views

How to extract a single road from OSM GIS Database?

Is there a way to extract a single row out of the OSM-GIS-Database (by a Query). It would be so much easier, if I could find an EER Model of the Database. I was searching a long term and found very ...
2
votes
1answer
174 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
142 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
400 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
164 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
272 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
239 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 ...
9
votes
2answers
8k 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
515 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
156 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
830 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
459 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
383 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 ...