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

learn more… | top users | synonyms

9
votes
2answers
2k views

What are the implications of invalid geometries

I've imported some data in a Postgis database and some of the geometries are reported invalid (ST_IsValidReason reports self-intersection or ring self-intersection). The queries I am performing don't ...
7
votes
1answer
3k views

Calculate distances between series of points in postgis

I have a postgres/postgis table containing a series of sequential points. I want to add a new column to the table which contains the distance between each point and the first one, and so forth until ...
6
votes
2answers
815 views

How to symbolize features with NULL values 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 ...
4
votes
4answers
4k views

Calculate distance of point from an arc/polyline

Using Postgres/PostGIS, I am trying to query the database for the closest arc to a particular GPS point. Each Arc is made up of two nodes that are GPS points on a map. Is there a way I could query ...
10
votes
2answers
855 views

postgis, extrapolate a line

I'm trying to extrapolate from a line segment to find a point on the line but a 3rd of the way 'back', i.e. trying to find point new, given points A and B below: Given a line, I can interpolate it ...
3
votes
0answers
194 views

How to estimate the average width of a sidewalk? [duplicate]

I have a table with two polygons representing urban block with and without the sidewalk: geomBlock and geomBlockWithSidewalk. On my city we can approximate the polygons of geomBlockWithSidewalk to a ...
8
votes
1answer
4k views

How to import GeoTIFF via postGIS into GeoServer?

As a simple test case, I'm trying to configure the software stack of OpenGeo such that I can display a GeoTIFF as an overlay to a normal map like OSM. So I perform the following steps: Create a ...
6
votes
2answers
11k views

How to connect OpenLayers to PostGIS data?

I have read in OpenLayers how to load KML, GeoJSON etc. vector files in OpenLayers. But how can I connect to PostGIS data. If not, why should I upload my data to PostGIS then instead of directly ...
4
votes
2answers
6k views

How to display a PostGIS table on Google maps?

I am attempting to take a shape file and display it on a map: I can do something like this: SELECT ST_AsText((ST_Dump(the_geom)).geom) as the_geom FROM shapes; But the problem is I still have to ...
3
votes
4answers
350 views

How to assign geometry to records in a table that contain an ID of a geometry point?

We are looking to map out 'incidents' at schools, but the incident table, which contains a few thousand records, only has a school number (plus the incident data). The table format is an Excel ...
3
votes
2answers
4k 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
887 views

Get a single cluster from cloud of points with specified maximum diameter in postgis

I have on the order of 2M Geometry Collections with N (median: 2, stddev: 6.6, 75th: 6, 95th: 21) Point objects (repeated points allowed and important) in a PostGIS table. I want to identify clusters ...
8
votes
2answers
2k views

Performance of Shapefile and PostGIS with GeoServer?

I'm going to build a web application, which will render around ten maps using GeoServer. I will be also changing the attribute data in the application for some layers. What is the recommended choice ...
6
votes
2answers
4k views

How to convert PostGIS table to Shapefile in Python?

I want to convert a PostGIS table into a shapefile (without using pgsql2shp). In order to create a geometry in the shapefile I have to give the Xmin,Ymin and Xmax,Ymax, and the geometry which I have ...
2
votes
2answers
89 views

Separating Overlapping DMAs by State using PostGIS?

I have two shapefiles. One is the DMA and another of states I know that some DMAs, such as Chicago will overlap between multiple states, for instance the Denver DMA overlaps states outside of CO and ...
1
vote
1answer
60 views

Transform to SRID 900913

How i can transform the geometry WKB for example: ...
1
vote
1answer
1k views

How to verify a PostGIS installation on OS X?

I am trying to set up GeoDjango and installing PostGIS on my mac and when I am trying to ./configure I am getting this error checking PostgreSQL version... PostgreSQL 8.4.9 checking libpq-fe.h ...
49
votes
8answers
10k views

How to create Vector Polygons at the same amazing speeds GISCloud is able to render them?

EDIT (outlining new bounty added): I have added the largest bounty possible to this question in hope of getting a very detailed answer. Your solution should outline a step-by-step guide utilizing open ...
20
votes
4answers
4k views

Simplifying adjacent polygons

I encountered a problem simplifying set of polygons that are adjacent. If I simplify each polygon separately with the Douglas–Peucker algorithm (which is used by many open source tools), the resulting ...
14
votes
5answers
2k views

Find pseudo nodes in free GIS software

Software gvSIG OA Digital Edition 2010 have tools topology for finding pseudo nodes in linear geometry. I set cluster tolerance 0.00002 and maximum number of errors -10000 for 20000 link count linear ...
21
votes
2answers
13k views

How to change the geometry type from Point to Multipoint within an existing table in PostGIS?

Is there a PostGIS function that can change geometry type for an existing table? We need to change from POINT to MULTIPOINT. The table will be empty when we change the geometry type and we cannot ...
18
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 ...
27
votes
4answers
15k views

What is the best way to join lots of small polygons to form a larger polygon?

I have the following layer using SRID 27700 in postgis: It's every administrative region in the UK, and (as you can see from the colour grouping) each of them has a text field specifying the county ...
18
votes
2answers
7k views

In PostGIS is it possible to create a View with a unique ID?

When I create a view in PostGIS is there any way to add a unique ID to that view? Just like the "gid" field in any other PostGIS table? Edit: Sorry I should have included this in the original post. I ...
16
votes
2answers
3k 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 ...
10
votes
2answers
2k views

How do you display related records in QGIS?

Coming from an esri background I have always used relationship classes in a gdb to display records related to a feature. Eg. Relate 1 survey point to many documents, photos etc. Does any one know how ...
12
votes
5answers
465 views

What in-depth spatial database systems tutorials exist?

Is there a good tutorial that explains in depth the internals of a GIS and a spatial database system such as PostGIS with examples and without any background assumption? I'm specifically looking for ...
14
votes
3answers
4k 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 ...
7
votes
2answers
2k views

How to get multiple shortest paths with PgRouting in one query?

I want to run shortest path algorithm on multiple source&target pairs at once and get a result as a table and process it then. How do I do this? This query does not work: SELECT a.source, ...
5
votes
2answers
1k views

How to perform SIA or Bezier line smoothing in PostGIS?

Can anyone provide an example SQL for smoothing linestrings from postgis table using Bezier curves or Iterative Averaging (SIA) algorithm?
10
votes
1answer
1k views

How to render a table with mixed geometry types in QGIS?

I created a table with geometry type "GEOMETRY" AND inserted data of point, line and polygon type. My question is how to show this spatial data Qgis not supporting for geometry type "GEOMETRY".
7
votes
1answer
6k 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 * ...
7
votes
2answers
5k views

Search functionality in Openlayers/Geoserver

I am using Openlayers,Geoserver & Postgis.... My postgis tables are having columns as id, name, POINT/POLYGON/LINE. Each table corresponds to a layer that I am showing in layerswitcher in OL. I ...
7
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 ...
6
votes
2answers
4k 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 ...
6
votes
1answer
4k views

How do I get a route between two points using PgRouting with TIGER2010 Data

A little background: I've got PostGIS 2.0 and all the TIGER2010 data loaded for a U.S. State California. I've got a table with points stored as data type geometry. I've learned enough to get the ...
11
votes
1answer
4k views

Merge Multiple Tables into a New Table in PostGIS

I am looking to merge a number of individual tables into a new table in PostGIS. This is an easy task when working with Shapefile data, but I am unsure how to do this in PostGIS. Any help would be ...
11
votes
2answers
6k views

Finding the closest Geometry in PostGIS

I've been looking over the PostGIS "API" of functions, and I notice that most of them take two elements to compare. For example, the ST_Distance function takes two geometry/geography elements to find ...
11
votes
3answers
2k views

How to interpolate GPS Positions in PostGIS

I have a PostGIS table of GPS positions for every five seconds: 2011-01-01 00:00:05, POINT(x1,y1) 2011-01-01 00:00:10, POINT(x2,y2) 2011-01-01 00:00:15, POINT(x3,y3) ... I'm looking for a query ...
5
votes
2answers
454 views

How to connect points (bus stops), which don't lie on the lines (LINESTRING), to a network?

I need to connect bus stops (points) to a network layer (OSM data). These bus stops do not lie directly on the lines (see screenshot) nor should their location be moved. I use PostGIS, pgrouting and ...
5
votes
2answers
1k views

How to group connected linestrings in PostGIS?

I have a table of streets I've selected based on a set of attributes (let's say it's speed_limit < 25). There are groups of streets that are locally contiguous; I'd like to group these sets of ...
4
votes
2answers
1k views

I want to edit corresponding postgresql data of a vector from openlayers- how to do it?

I want to edit the corresponding text/numeric data type columns of a vector through openlayres, when a user click/mouse over a vector. like, http://dev4.mapgears.com/bdga/bdgaWFS-T.html# any ...
3
votes
4answers
9k views

Return all results within a 30km radius of a specific lat/long point?

I have a table with a column the_geom which contains data similar to: ...
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
2answers
2k views

Connect to postgres localhost in QGIS

I am firly new to actively work with databases. I wanted to try out some stuff on my home computer where I have installed pgadmin and postgres previously. I can connect to my localhost in pgadmin but ...
7
votes
1answer
5k views

qgis to local postgis connection - fails

It seems pretty clear I cannot connect to a local postgresql installation out of QGIS. This is my first experience with PostGIS. I am using PostgreSQL 9.0 on a win7 machine. I can see the successful ...
4
votes
1answer
336 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
2answers
2k views

What is this PostGIS query doing to show great circle connections?

I'm trying to draw a flight map in CartoDB. My starting point is a single lat long point. We'll pretend it is this: ST_GeomFromText('POINT(-71.060316 48.432044)', 4326) And I want draw lines ...
1
vote
2answers
772 views

take from multilinestring the start & end points

I have to use the A* algorithm and my geometry is in multilinestring and i want to take stratpoint(x1,y1) and endpoint(x2,y2) . I use this queries but the colums after the execute is still empty ...
1
vote
2answers
3k views

How to select all points within a circle in PostGIS?

I have set of points in a PostGIS Table (Type Geometry). Now, I need to filter points that lie in a circle with a center (C) and radius (R)! I expect C to be of type Point or Coordinates.. R to be ...