4

UPDATE: To try to make this a little more simple, is it possible to create a query which selects a group of points such that each point in the group is at most X units away from at least one other point in the group? If that is possible, then can it be extended to return more than one row such that each row is a group of points as specified above?

I'm starting to think this isn't possible, so I'm working on some java code to do it, but I still have some hope. Original post and info below:

I have a postgresql / postgis table with a grid of weighted points, and I'd like to essentially create a contour map of those points. My goal is to get one or more polygon that surrounds points of a certain weight, and then repeat for other weights. I'm going to convert those polygons to KML for display, which I can figure out later and is only relevant because I don't necessarily need this to be something dynamic or efficient - I can do it manually and save off the polygons at each step.

I've read a lot of other posts about this but they all seem to require additional libraries or software, and my points are already in a grid so the ST_SnapToGrid solution doesn't make sense to me. Is there a strictly postgresql solution?

My thought is that there would be a query that creates a polygon (using ST_ConvexHull or ST_ConcaveHull) around points that are within some distance from each other (which should be a set value because my points are already equally spaced on a grid).

Example lat/lons/weights (for distance reference):

41.96892, -87.9351, 1904
41.97392, -87.9351, 1940
41.97892, -87.9351, 1978

Table:

CREATE TABLE temp_weighted_points
(
  id serial NOT NULL,
  latitude double precision,
  longitude double precision,
  weight double precision,
  type character varying(10),
  geom geometry(Point,4269),
  CONSTRAINT temp_weighted_points_pkey PRIMARY KEY (id)
)

I've tried this link (among others) but can't get any of those solutions to work: https://stackoverflow.com/questions/13087288/separate-gis-points-into-groups-of-points-that-are-close-to-each-other

Any suggestions?

EDIT:
Regarding what the data looks like, it's a grid of drive times over the city of Chicago. To create the data, I found a boundary shape file of the city of Chicago, then used a postgresql query to create a grid of equally spaced points within that boundary / polygon (for pictures and the query, see How to create regular point grid inside a polygon in Postgis?). Each point is separate by .005 lat / lon units (degrees? not sure what it's called) as can be seen in the example lat/lons above. Once I had these points, I used to google's drivetime api to generate drive times from each of the points to four different locations, and then took the smallest drive time between the four locations and stored that data as the weight for the grid point (the weight is in seconds, so the first example point above has a drivetime of about 31 minutes). That's what I have at this time - points that are equally spaced over an area / polygon and that each have weights / heights associated with them.

What I'm trying to get is a contour map of this data, which will basically look like the city of Chicago with 4 depressions (the locations) or an upside down mountain with four peaks, because points on the grid close to the locations will have very small / short drive times, while points farther away will be higher.

  • 2
    Really interesting problem there. It may be worth looking at published research papers, sometimes you can learn a lot about techniques used to solve problems from them, then translate them to apply to the tools you're using. – Craig Ringer Feb 9 '14 at 23:33
  • 1
    "Can't get it to work"? What have you tried, what errors do you get. As for the problem: Is it similar to a contour map of terrain height? A visual example may be better for the ones like me. – Jakub Kania Feb 10 '14 at 13:24
  • "Can't get any of the solutions to work", as in, they don't produce the results that I'm looking for, and I can't figure out how to modify them to my benefit. I didn't include what I had tried there because I had tried many different things and wanted to consolidate to one type of solution: a postgresql query. But for your second question, I'm going to edit the post to provide some extra information about how I built the data. – Brendan Feb 10 '14 at 19:26
2

I'm pretty new to GIS but have been working with way to create gravity models (also called the 'huff model') for retail. It sounds like a similar problem to the one you are trying to solve. I haven't been able to find a postgis solution as of yet, but looking at the approaches taken with other products may help you. For example this solution uses maptitude:

http://www.youtube.com/watch?v=9d0Ccsj2Ct4

And I have seen other examples using mapinfo vertical mapper and a huff model extension for arcgis.

Following the gravity model example you have completed most of the initial steps and just need to find a way to visualize the data, in the maptitude example I believe they use some kind of heat map plugin for raster images, you can probably achieve the same thing with a QGIS contour map, but I haven't a solution that solely uses postgresql/postgis.

1

This is an old question (2 full versions of Ubuntu old; at least 4 'point' versions of QGIS old; several GB of Microsoft CRITICAL-security-hole-updates old).

However as with the age-old question of why sheep are found in trees, it is a fair question - and one that in recent weeks has been much on my mind.

What the OP seemed to be trying to create is a variety of isochrone map (it would be an isochrone map if the contours were equal time-intervals).

This is something I've done in the past using road polygons that are usualLy readily available from .gov statistical authorities.

The methodology I use (which I kludged together from others' work - thank you, internet! [and especially underdark] ) goes as follows:

  • fetch all the roads within some radius of the target point of interest;
  • build a node and edge network;
  • calculate the 'cost' (e.g., travel time assuming a speed) between each pair of points in the grid (using the PostGIS add-in pgrouting);
  • make a set of convex hulls with the outer edge equal to the travel times you want;
  • find all points in some other dataset (addresses, cadastre polygons - any spatial data - where the data lies within one of the convex hulls and outside the next one inside it (there's probably a WAY better approach than this step);
  • return the data as GeoJSON and convert it to topoJSON using Mike Bostock's topoJSON node.js library.

For a radius of about 1km around a point of interest, this approach takes about 1.3 seconds to make the hulls and fetch, style, sort and render every property boundary polygon (that is about 1200 polygons for an average town). That's quick enough to permit real-time user control over the centre of the isochrone without even having to code in a 'spinner' to divert attention from the delay - on a shift of the focal point the topoJSON is ready and served before the GMap tiles.

The result looks like this on a GMap (warning: I have the aesthetic design flair of an autistic rhinoceros) -

quite purdy

the contours themselves look like this in QGIS

enter image description here

In both cases the purple circle is a circle of radious 700m (the aim of this was to show that circles are stupid for pedsheds; the yellow isochrone boundary is a 10-min isochrone at the same walking speed as the purple circle).

1

Hm, handle a point cloud and making some regular stuff like non convex sourrounding polygons depends on the distribution in space of the points itself. So what I do is (and it is IMO brute force approach):

Make a triangulation (it gives you also the convex hull).

If the distances are in most cases are regular, build the average distances and throw away the triangles which have segmentes that are larger then the standard distribution times N.

And resample the polygons by a union operation of the remaining triangles.

So you can get a non convex hull, but brute force.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.