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

learn more… | top users | synonyms

15
votes
2answers
2k views

How can I merge/collapse nearby and parallel road lines (eg. a dual-carriageway) into one single line?

I have a shapefile from OSM which contains all the roads within a city. Larger roads (like dual-carriageways) seem to have 2 parallel lines. I'd like to collapse / combine those into 1 line so I can ...
12
votes
3answers
3k views

Optimizing a nearest neighbor calculation using PostGIS

I'm using PostGIS to calculate the nearest neighbors of polygons. What I want to calculate is the minimum distance from each polygon, to the nearest polygon. Thus far I have got great help from Mike ...
9
votes
1answer
2k views

One to Many Relationship in QGIS with PostGIS

Does anyone know of any tool that can do a One-to-Many relationship in QGIS. I have my data in a PostGIS database. I would like to be able to query spatial layers that are related to spatial or non ...
15
votes
7answers
3k views

How to migrate data from ArcSDE to PostGIS?

Its been suggested that i ask this question again, but perhaps worded differently than how to Bulk load multiple shapefiles into PostGIS. I'm new to postgis and sql but i want to use postgis as a ...
23
votes
3answers
4k views

Meaning of Simplify's tolerance parameter

The standard parameters of Douglas-Peucker's simplify algorithm are geometry and tolerance (e.g ST_Simplify in PostGIS). What's the meaning of the tolerance parameter? I know that the bigger the ...
18
votes
5answers
1k views

How to simplify a routable network?

I have a network graph that I need to simplify in the sense of reducing the number of edges. The idea would be to merge nodes that are located close together and remove the connecting short edges. ...
13
votes
6answers
15k views

How can I enable Shapefile GUI Loader in pgadmin3?

I have installed PostgreSQL 9.1 with PostGIS 1.5.3 on Ubuntu 11.10. I also installed pgadmin3 1.14.0-beta1, following this instructions. If I remember correctly, when I installed PostGIS on Windows ...
10
votes
2answers
10k 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
1answer
652 views

calculate slope for a road network

I am working on a road network for routing operations using postgis and QGIS. I am new to both of these tools and need to calculate the slope for each edge (by retrieving the Z value for the start ...
6
votes
1answer
616 views

Filling a polygon with lines using Postgis

i need to calculate a specific distance in an area. The distance is calculated from parallel lines in a polygon, And i need to find out how many parallel lines (with x meters distance to each other) ...
31
votes
2answers
1k views

What coordinate system should be used to store geography data for celestial coordinates?

I am doing an astronomy project. I want to have the information about our images stored in a spatially enabled database. This, I would think, should be a very easy special case for GIS functions ...
12
votes
5answers
1k views

Large shapefile to raster

I have a large shapefile (1 gb) and need to rasterise it. I have tried the following already. 1.) Import it into GRASS using v.in.ogr this failed with the error message: ERROR: G_realloc: unable to ...
8
votes
1answer
5k views

How to create a table from existing tables (applying new schemas) in postgis DB?

I have a table that is geospatially enabled; that contain world borders. I want to break it down to different schemas (global, europe etc..) . My thought is to use use something that looks like this: ...
6
votes
4answers
2k views

Point in polygon algorithm for multiple polygons

I've a Google map with bunch of polygons on it. Here is a problem I'm interested in: Given a lat,lng point, what's the best way to determine all the polygons that this point lies in? The obvious way ...
5
votes
2answers
3k views

Using OpenLayers (with OpenStreetMaps) and PostGis for rendering data acquired from GPS tracking device?

I'm researching and planing to acquire data from GPS device and store them in postGIS database, then use geodjango to process/work them out and finally chart them on a map with OpenLayers. Will these ...
20
votes
6answers
5k views

How do you best deal with data that spreads across UTM Zones?

I have data that spreads across two UTM Zones GDA/MGA94 UTM Zone 55 and 56. I have layers such as a cadastre, roads, pipelines etc where I want to store measurements like lengths and areas in metres ...
8
votes
3answers
972 views

Retrieve polygon smallest MBR

I have a postgis table containing polygons. I want to transform my polygons to 4 points polygons : retrieve the smallest MBR. Here comes an illustration of what I want : My original polygon is ...
7
votes
3answers
533 views

Is there a paid service for accessing a PostGIS/OSM DB

I want to render a world map with mapnik and postgis, but do not want to set up my own DB, because the needed hardware and processing time for the OSM import make it nearly impossible for me. I just ...
6
votes
2answers
1k views

Storing GPS tracks with timestamps in PostGIS

I need to store a series of GPS points with timestamps in the database (traces of various vehicles). I am wondering which of two approaches to choose for storing them in the PostGIS table: 1.) Each ...
6
votes
1answer
1k views

What is a “non-noded intersection”?

Can someone provide a precise definition of what a "non-noded intersection" is? There are various GIS platforms and toolkits that have errors with this text, and Google search results for this tend to ...
6
votes
1answer
5k 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 * ...
3
votes
2answers
3k views

Count Points in Polygon with Postgis

I've got a simple problem: I want to count the number of points within a set of polygons. I have a SQL already but it only gives back the gid of the polygone that actually contains points. My ...
2
votes
3answers
3k views

Render vectors from OSM data in POSTGIS database

I'm a newbie when it comes to mapping application. I had successfully loaded my OpenstreetMap data in a Postgresql/PostGIS database. Now my problem is how to render the vector data (the POI's, ways, ...
7
votes
4answers
517 views

Is there a way to identify and remove similar GPS tracks in PostGIS or ArcGIS?

I have a large number of GPS tracks from a number of different users. I'd like to identify tracks that take the same route and remove those duplicates. For example, a user takes the same streets from ...
6
votes
3answers
3k views

PostGIS non functional on Ubuntu 14.04

I am unable to create the PostGIS extension on new databases in Ubuntu 14.04: $ psql pg_testing -c "create extension postgis" ERROR: could not load library ...
6
votes
3answers
2k views

How to create spider diagrams (hub lines) in PostGIS?

I'm rather new to PostGIS but I have done a lot of reading. One thing that I have been searching for is a function that allows users to create a "spider diagram" (also called "desire lines" or "hub ...
4
votes
1answer
812 views

How to create linestrings with a definite angle and length, that are fixed to a point

I want to create linestrings with a definite angle (e.g. 160°) and length (e.g. 2m) that are fixed to a number of points of another linestring. So, I want to use the ST_DumpPoints function to find the ...
4
votes
3answers
1k views

How to speed up Postgis raster layer display in QGIS?

i've been importing some digital orthophoto geotif files into postgis 2.0.1 via raster2pgsql tool (i also made 3 levels of an overview table - pyramid). Then i tried to import the layer into qgis via ...
4
votes
3answers
2k views

Newbie PostGIS Geometry and Multilinestring Clarification

I'm having some trouble figuring out if I've got the right data loaded for PgRouting. Using the bash script included in PostGIS 2.0, I loaded Tiger2010 data for U.S. State California. The edges ...
3
votes
1answer
225 views

nearest facility with pgrouting

I have a postgres table of students with lat, lon and geom, , and a table of schools with the same, and a table of roads that has been converted to nodes. I'm trying to find the driving distance to ...
3
votes
1answer
127 views

Nearest neighbour for every row

I'm relatively new to PostGIS and postgresql . I'm trying to build a simple query containing two relations as follows : I've two relations R1 and R2 both of which contains geometries(Points) . I ...
3
votes
1answer
7k views

Transforming from SRID 900913 to 4326 with PostGis

I have two Postgres tables with different SRIDs: 4326 and 900913. I want to convert the latter to 4326. I first changed the SRID using: Select UpdateGeometrySRID('table', 'geomcolumn', 4326). I ...
3
votes
3answers
1k views

How to Calculate length of polyline geometry for several tables in PostGIS?

PostGIS database contains several geometry (polyline) tables (with names "D1_r", "D2_r", "D3_r"). I calculate length for one "postgis layer" use request: SELECT ...
2
votes
1answer
484 views

PostGIS Raster in MapServer

I'm having trouble getting a PostGIS raster to load and display in MapServer. I'm not sure, but suspect it's a version problem. My mapfile layer and the error message are shown below. LAYER ...
2
votes
1answer
235 views

How to split polygons with another polygon and inheritage data?

Let's say that we have two tables with polygons representing areas. Table A and Table B. Geometries from table A represents both the valid and invalid areas. And the geometries in table B represents ...
2
votes
2answers
2k views

What Spatial Reference System do I store Google Map's Lat/Lng in

I am new to PostGIS, Spatial Reference Systems and projections and want to store lat/lng coordinates retrieved from Google Maps and Openstreetmap and its services like the Geocoder/Nominatim. I'm ...
2
votes
1answer
301 views

Hyperlinking to PostGIS Layers and Zip files with QGIS Actions

I previously asked a question about hyperlinking data in QGIS that gets loaded into QGIS (see post: Hyperlinking in QGIS - Add Data to current window). A user provided me with the code, which works ...
10
votes
3answers
2k views

getting dxf files out of PostgreSQL

I've successfully loaded a bunch of shapefiles into a PostGIS database. I would like to be able to retrieve specific geometry and layers as dxf files based on SQL queries. I'm using PostGIS, GDAL, ...
9
votes
2answers
1k views

Growing a PostGIS geometry by a percentage

I'm looking for a way to add a buffer around a PostGIS geometry, but the size of the buffer should depend on size of the geometry. That is, I want each geometry to be enlargened by, say, 5%. The idea ...
8
votes
2answers
4k views

PostGIS ST_Buffer Radius Help

I am trying to find all points within a five-mile radius of a given point. I have a query like this: SELECT * FROM table WHERE ST_Contains(ST_Buffer(geomFromText('POINT(0 0)', 4326), ?), latlon) I ...
7
votes
1answer
1k views

What is difference between ST_DWithin and ST_Distance for proximity search in PostGIS?

I have records stored in a table with latitude/longitude coordinates stored in a geometry field. I want to find all records nearby a user supplied reference point. Note "nearby" probably means less ...
7
votes
1answer
618 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 ...
6
votes
2answers
1k views

PostGIS - area-weighted calculation on an intersection

I'm trying to learn postgis sql by performing some familiar operations from ArcGIS. Here, I want to perform an area-weighted calculation of certain populations on the intersection of a set of parcels ...
5
votes
1answer
216 views

How to transform a set of street-segments into city-blocks, with PostGIS2?

Theoretically is possible to obtain the polygons of city blocks (urban blocks) from streets, when streets are represented by street axis (LineStrings). The urban blocks are bounded by streets, so ...
5
votes
1answer
3k 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 ...
4
votes
1answer
229 views

Calculate distance between point and linestring and how to represent that on QGIS?

I have loaded my data with osm2pgrouting and I have my network with ways (Linestring) and nodes (Point). The problem is : I have a database of doctors addresses (point) and I should calculate the ...
4
votes
3answers
1k views

Loading raster data to postgis

I'm a newbie in GIS technology and need some help in loading a rainfall radar raster data into PostGIS. The raster image is shown here: What I need is to store in the database the radar ...
4
votes
2answers
1k 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
278 views

Symbolising null features in graduated symbology

I have a polygon feature dataset, and an attribute from a different table that I join (one-to-one) within QGIS in order to symbolise the attributes as a choropleth map. However, not all polygon fields ...
3
votes
1answer
395 views

ST_Equals Postgis problems

I am trying to find overlapping lines using ST_EQUALS on two tables This query returns 0 rows: SELECT * FROM geom1 c, geom2 h WHERE st_equals(c.simple_geom, h.simple_geom); This query runs ...