Spatial Partitioning in PostGIS

I’ve been meaning for a long time to see what an implementation of spatial partitioning in PostGIS would look like, and a trip next week to the Center for Topographic Information in Sherbrooke had given me the excuse to try a toy implementation.

Imaging map data constrained to a 1km square. Here is an example that partitions that square into a left- and right-side, and then inserts the data appropriately into the right table as it comes in. Features that straddle the two halves get put into a third special-case table for handling overlaps.

-- parent table
create table km (
id integer,
geom geometry

-- left side
create table km_left(
check ( _st_contains(st_makeenvelope(0,0,500,1000,-1),geom ) )
) inherits (km);

-- right side
create table km_right(
check ( _st_contains(st_makeenvelope(500,0,1000,1000,-1),geom ) )
) inherits (km);

-- border overlaps
create table km_overlaps(
check ( _st_intersects(st_makeline(st_makepoint(500,0),st_makepoint(500,1000)),geom ) )
) inherits (km);

-- indexes
create index km_left_gix on km_left using gist (geom);
create index km_right_gix on km_right using gist (geom);
create index km_overlaps_gix on km_overlaps using gist (geom);

-- direct insert to appropriate table
CREATE OR REPLACE FUNCTION km_insert_trigger()
    IF ( _st_contains(st_makeenvelope(0,0,500,1000,-1),NEW.geom) ) THEN
    INSERT INTO km_left VALUES (NEW.*);
    ELSIF ( _st_contains(st_makeenvelope(500,0,1000,1000,-1),NEW.geom) ) THEN
    INSERT INTO km_right VALUES (NEW.*);
    ELSEif ( _st_intersects(st_makeline(st_makepoint(500,0),st_makepoint(500,1000)),NEW.geom) ) THEN
    INSERT INTO km_overlaps VALUES (NEW.*);
    RAISE EXCEPTION 'Geometry out of range.';
    END IF;
LANGUAGE plpgsql;

CREATE TRIGGER insert_km_trigger
    FOR EACH ROW EXECUTE PROCEDURE km_insert_trigger();

-- add some data
insert into km (id, geom) values (1, 'POINT(50 50)');
insert into km (id, geom) values (2, 'POINT(550 50)');
insert into km (id, geom) values (3, 'LINESTRING(250 250, 750 750)');

-- see where it lands
select * from km;
select * from km_right;
select * from km where st_contains('POLYGON((20 20,20 60,60 60,60 20,20 20))',geom);

It’s still a toy, I need to put more data in it and see how well the indexes and constraint exclusion mechanisms actually work in this case.

ESRI FGDB API: No Servers need Apply

When downloading the ESRI file-based geodatabase API you are required to accept a license agreement which includes this lovely clause in the section about acceptabe uses of the API:

“Single Use License.” Licensee may permit a single authorized end user to install and use the Software, Data, and Documentation on a single computer for use by that end user on the computer on which the Software is installed. Remote access is not permitted. Licensee may permit the single authorized end user to make a second copy for end user’s exclusive use on a portable computer as long as only one (1) copy of the Software, Data, and Documentation is in use at any one (1) time. No other end user may use the Software, Data, or Documentation under the same license at the same time for any other purpose.

Note, only one user may use the software, and remote access is not permitted. That is, you can’t run a mapping server on top of this API, because then multiple users would be using it, remotely. Impressive. Point to Redlands. I will console myself in the usual way: “better than nothing.”

Update: Martin Daly asked me to take a second look, and someone Twittered asking if the clause was boilerplate. On second examination, it could simply be boilerplate used for other desktop-oriented software that has been used without thought as to how it muddies the uses for non-desktop systems. The full text is here for those with ESRI logins. Never attribute to malice what you can attribute to accident, another good rule to live by (but how empty I would be without my little conspiracy theories!). The precautionary principle would dictate not web-serving with it until/unless it is clarified, but in the meanwhile bulk loaders and translators could certainly be put together.

Update 2: Though the I find the license language somewhat unclear, the intent appears to be to allow any application to sit on top of the API. Crisis averted, situation normal.

Update 3: Just got off the plane and the ESRI folks have left me a nice voice mail explaining that the intent of the clause is with respect to developers downloading it (once, for one person) not for uses of derived products. The moral of the story is that ESRI people are pretty darn nice, and I am not so very nice. Something for me to work on in the new year.

Update 4: Received email from ESRI confirming that the final license will be reviewed to ensure there are no ambiguities and that it reflects their intent that the API be usable by any application in any application category and the derived product freely redistributable and royalty free. So to the extent that the current license has any ambiguity it shouldn’t be considered a red flag that the final one will.

BC: Power up your Vote!

I gave this spiel at the Victoria GeoGeeks meet-up last week, but it’s worth giving to the world at large: if you want your vote to count in choosing your representatives, exercise it at every opportunity! For those in the USA, that means participating in primary elections, and for us in British Columbia right now that means participating in the party leadership contests.

When you participate in early elections like primaries or leadership races, your vote gets more powerful.

In the last British Columbia election (2009) 1,651,567 people voted to choose the government. But they didn’t get to choose the Premier, they just got to choose the party or local candidate. The next Premier of BC will be the leader of either the BC Liberals or the BC NDP. The combined membership of those races will probably be on the order of 60 thousand. So a vote cast in the leadership contests is about 27 times more determinative of who will be the Premier than a General Election vote.

Who wouldn’t want their vote to be 27 times more powerful?!? But here’s the rub: only party members get to vote.

For the BC Liberals, the election will occur on February 26, but the cut-off for membership is 5PM on February 4. You can sign-up online here ($10 annual membership).

For the BC New Democrats, the election will occur on April 17, but the cut-off is midnight on January 17, and you can sign-up online here ($10 minimum donation).

If you live in BC, this is your chance to have an outsized influence on who makes the decisions for our future. Get to it! I have!

We interrupt this geeky spring...

To do something completely different. British Columbia, my fair home, is in the midst of an odd period where both major parties are having leadership races. The race for my party of choice (the NDP) has just gotten started, and I believe that pitching in early for what you believe in is a way to have maximum effect. So I’m spending my spring working as much as possible for John Horgan. John’s someone who reflects my belief nobody has all the right answers, and he’s someone with the personal skills (that I envy) to both listen for the answers and bring folks together around a common direction. And he’s fun to be around, which is a nice bonus.

Unfortunately for PostGIS, time doing campaign work takes from time spent on PostGIS 2.0, so unless another PSC member has an equal and opposite infusion of time to mine, things will slow down a bit for the next couple months. I’ll still be going to the Montreal code sprint though: some things are sacrosanct.

Victoria GeoGeeks!

The Victoria geo-geeks are meeting again next week, on Thursday, January 6, at 6pm at the offices of Latitude Geographics. We’ll be hearing from four speakers, on the topics of Google Fusion Tables and Open (Geo) Data in Government.

  • Google Fusion Tables, Intro and Demo - Josh Livni, Google Outreach Engineer (30m)
  • Open (Geo) Data in BC - Herb Lainchbury and Greg Lawrence (25m)
  • Open (Geo) Data Rebuttal - Mark Sondheim (5m)

Hope to see all you Victoria geo-geeks there!