PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.

learn more… | top users | synonyms

1
vote
1answer
2k 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
3k 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 ...
1
vote
1answer
217 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,
10
votes
3answers
1k views

How to speed up queries for raster databases?

I have a raster database in postgresql/postgis with these columns: (ID, rast, data_of_data). 'rast' is the column that has raster files in WKT format. An example query to find the DN value of a ...
3
votes
3answers
656 views

How to find SRID for random polygon?

I'm testing spatial database performance on a dataset. Spatial objects in this dataset (stored as WKT in a flat text file) are random valid polygons which have vertex coordinates (x,y) where 100,000> ...
1
vote
1answer
338 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
159 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
762 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 ...
15
votes
4answers
26k views

How to get coordinates from geometry in PostGIS?

This line: SELECT geom FROM myLayer.geom will return a hex value, i'm wondering how would it be possible to get decimal values, a result that look likes human-readable coordination? Regards,
2
votes
2answers
518 views

How fill length and feature count in PostGIS table for each polygons

I'am new to PostGIS/PostgreSql. I have a database which containts two shape-files : polygonal layer towns and linear layer of roads To calculate the length and feature count of 'Streets' inside ...
5
votes
1answer
582 views

How can you join attributes from polygons to points which are within the polygon feature using PostgreSQL and PostGIS? [duplicate]

Possible Duplicate: How to perform a spatial join of point and polygon layers in PostGIS? I don't know how to solve my actual problem. I have polygons and points (some points are located ...
5
votes
2answers
886 views

How to recreate unique reference number with QGIS or PostGIS?

I have a shp file with about 500 odd records in it. It has an ID field but I want to add in a new field with a unique reference number. Can I use the field calculator to generate a unique reference? ...
2
votes
1answer
732 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 ...
1
vote
0answers
189 views

How to config geoserver to read style of vector layer from postgis/SQL store?

I've imported vector layers from map info pro to both SQL server and post gis using easy loader. In both cases I've included style column in the exported table. When I create new store in geoserver ...
5
votes
1answer
284 views

PostGIS ST_Rotate distorts polygon

I'm trying to rotate a box around its southwest corner using PostGIS. However, I am not getting the expected results when doing the rotation. I'm using SRID 3785. The image below shows what ...
3
votes
2answers
1k views

General GeoDjango Opinion

I'm forced to crawl out of my intranet and create a public GIS site with quite an extensive dataset. All of my data is contained within PostGIS. I started dabbling with Geoserver/Postgis/OpenLayers ...
2
votes
3answers
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 ...
1
vote
0answers
266 views

How can I use Convex Hull to select an area with a space from the points?

This refer to this post : Read Here My goal is to select an area covering some points, but maintaining some space from points of border, like in the follow picture : I think ST_Convexhull will do ...
7
votes
1answer
622 views

How to set up GeoDjango with PostGIS 2.0?

I would like to run GeoDjango (Django 1.4) with Postgis 2.0. Whenever I try to run syncdb I get the following error for geographic fields: Failed to install index for stores.Store model: operator ...
2
votes
1answer
440 views

Detect city from coordinates

I need to know how to detect which city belong certain coordinates. Wich is the best way to do this? Thanks in advance. Pd: excuse my english
2
votes
0answers
660 views

why preview image is blank for ImageMosaicJDBC (postgis raster) layer?

I follow http://docs.geotools.org/latest/userguide/library/coverage/pgraster.html to publish a ImageMosaicJDBC layer in geoserver. the layer seems published ok and saved, but the preview of this layer ...
2
votes
1answer
273 views

GeoServer: Show (Random?) Subset of Features

Background We have a GeoServer layer backed by a PostGIS table with 22 million smallish features covering the continental US. We're using GeoServer to serve the features as a WMS layer to a client ...
4
votes
2answers
575 views

How to remove redundant points using Open Source GIS (preferably)?

We have points table including vehicle routes.In huge traffic conditions,devices collecting data on route may output redundant points where at any coordinate same points may repeat.We can easily ...
5
votes
4answers
2k 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 ...
5
votes
2answers
154 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 ...
4
votes
3answers
471 views

Why is a stored procedures much slower than the single query?

I wrote a normal query to snap the next way point from a pair of coordinates in EPSG4326, nothing complex. To optimize the performance a bit I made a boundary condition to minimize the points needed ...
2
votes
1answer
164 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. ...
6
votes
1answer
583 views

Geometry errors when importing data from ESRI file geodatabase to PostGIS

When I run the following command to import by data from an ESRI fgdb to postgis... ogr2ogr -f "PostgreSQL" -a_srs "EPSG:28355" -t_srs "EPSG:3857" PG:"dbname=tp_test user=postgres port=54321 ...
2
votes
1answer
948 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: ...
2
votes
1answer
191 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
160 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
523 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
1answer
284 views

Unable to add PostGIS table to Geoserver

I am able to add individual tables to Geoserver using, curl -u admin:geoserver -v -XPOST -H 'Content-type: text/xml' -d ...
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
173 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 ...
3
votes
2answers
917 views

How to debug 'Layer is not valid error' from Python plugin?

I'm trying to understand what goes wrong with a QGIS python plugin. Basically I'm trying to render a series of POINT. The query works fine, at least giving it by hand from inside PostGIS DB: "SELECT ...
5
votes
3answers
3k views

How to extract POINT from a [LINE|MULTILINE]STRING

I'd like to create a vectorLayer in QGIS to display all the points that form a LINESTRING or a MULTILINESTRING stored in a PostGIS DB. I think that I need to transform all points of LINESTRING (or ...
2
votes
0answers
290 views

From pgRouting shortest_path back to osm node id

I'm successfully performing path finding queries like shortest_path on data imported with osm2pgrouting. The tables I have inside my pgrouting DB are the following: List of relations ...
1
vote
2answers
289 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 ...
6
votes
2answers
254 views

Join multiple spatial tables by distance

I'm trying to join two tables, one which contains road names and the other just a point. After exhausting research I'm now stuck. The idea is to join up the two tables using only distance, ie, select ...
3
votes
1answer
2k views

Creating osm2pgsql tables in PostGIS database

I managed to partially setup osm2psql to work with my PostGIS DB. My DB contains now the following tables: Schema | Name | Type | Owner --------+-------------------+-------+------- ...
3
votes
2answers
163 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
860 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 ...
1
vote
1answer
278 views

Howto setup osm2psql

I'm following these instructions to setup osm2psql . I need it to import osm data file inside a PostGIS DB. The following steps worked fine: createdb -E UTF8 -O user mydb createlang plpgsql mydb I ...
5
votes
1answer
183 views

How to convert PostGis Table (Grid data) to Continuous field using Qgis?

I'd like to transform my Gridded Layer (generated form a PostGIS table of points) to a continuous and colored layer. How could I do this with Qgis or any other program?
4
votes
3answers
593 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 ...
4
votes
2answers
348 views

PostGIS or SQL 2008 ? (For the newbie GIS programmer but familiar with .Net) [closed]

I've been using MS SQL for years. Starting now to develop GIS application and it is required to do queries and store vectors created in OpenLayers in DB. Assuming I'm developing over .Net , What will ...
1
vote
1answer
186 views

Is it possible to export raster data from PostGIS or MapServer as a base64 string?

Is it possible to output a PNG in a base64 string so that can be directly loaded into a browser? I've read through the PostGIS documentation which refer to the GDAL output formats. The GDAL output ...