Joinery Test

Joinery Test

Blog PostGIS Basics - Part 2 - Hello World

Jeremy Tunnell at 

SELECT AddGeometryColumn('public', 'table','column', 4269, 'POINT', 2)

This will add a new column to the table and add an entry to the metadata table (the 4269 specifies my reference system, more on that below). It will also add a column to "table" and several constraint checks on the new column.

Next, you'll need to know about spacial reference systems. You'll notice that the letters SRID are scattered throughout the documentation. SRID stands for "Spacial Reference ID", and it describes the system to use to transform a round world into a flat x,y coordinate system. (Interestingly enough, Latitude and Longitude are not x,y coordinates but instead radians.) There are literally thousands of them.

I chose to store my geocoded addresses in two formats: SRID 4269 (Lat/Lon) and SRID 2163 (US National Atlas - meters). That way I can do my distance calculations quickly using the x,y coordinates (for approximate accuracy) and i'll have the lat/lon to feed to, say, Google Maps.

So now i'll need to add the second column:

SELECT AddGeometryColumn('public', 'table','column2', 2163, 'POINT', 2)

Inserting Data

My data is actually coming from the Yahoo geocoding api. I'm feeding them an address and they are returning a latitude/longitude. I used the following SQL to update my addresses table:

UPDATE addresses SET
coordinates_ll = ST_SetSRID(ST_MakePoint(" . $point['longitude'] . "," . $point['latitude'] . "),4269),
coordinates_proj_m = ST_Transform(ST_SetSRID(ST_MakePoint(" . $point['longitude'] . "," . $point['latitude'] . "),4269), 2163)
WHERE id=" . $id;

What we are doing here is, from inside out:

  1. Create a point.
  2. Set its SRID to 4269 (lat/lon)
  3. AND for the second query, the point is converted to SRID 2163
  4. Store it.

Note, the longitude comes first in these functions.

A quick distance query

Show me the ids of records that are within 5 miles (8000 meters) of a point.

SELECT id
 FROM addresses
 WHERE Distance(coordinates_proj_m, ST_SetSRID('POINT(-1952333 -571399)', 2163)) < 8000

To get this to work, you must transform your point into the same SRID as the column you're querying.

It also helps to put an index on the column we're querying on:

CREATE INDEX idx_coordinates_proj_m
ON addresses
USING gist(coordinates_proj_m);

Note: This does not scale.

References

Add Comment