[postgis-users] Cut off 1-acre of a parcel

[Obe, Regina]

John,
Just curious - how slow is slow and what kind of settings do you have on
your server.

So I presume you are trying to increment pseudoParcel from 1 -> ?;

I think the easiest way to do this is in a plpgsql function - something
of the form

CREATE FUNCTION cp_runsim(pstart int, pend int) RETURNS void AS
$$
DECLARE i int;
BEGIN
	i:= pstart;
	WHILE i < pend
		UPDATE my_parcel_shards
    		SET pseudoParcel = i
    			FROM bestPositionOption b
		WHERE my_parcel_shards.parcel_no = b.parcel_no
    		AND my_parcel_shards.shard_hpos <= b.shard_hpos
    		AND my_parcel_shards.shard_vpos <= b.shard_vpos
    		AND pseudoParcel IS NULL;

		i := i + 1;
	END LOOP;
END;
$$
   LANGUAGE 'plpgsql' VOLATILE;

Then you would call it as follows

SELECT cp_runsim(1,5);

Hope that helps,
Regina

-----Original Message-----
From: John Abraham [mailto:jabraham at ucalgary.ca] 
Sent: Wednesday, April 16, 2008 7:18 PM
To: postgis-users at postgis.refractions.net
Cc: gdt at ir.bbn.com; Obe, Regina
Subject: Re: [postgis-users] Cut off 1-acre of a parcel

Thanks Greg Troxel and Regina Obe for the input.

Based on Regina's ideas, I have something working.  Code is attached.  I

have it working for 2 example parcels, one roughly square and convex, 
the other irregular and non-convex.  Map also attached.

I think I could modify this to take away some larger percentage of land 
on originally larger parcels, to apply a "net land use", to account for 
local roads and such, as Greg suggests.  I could also work from any 
corner or side (instead of the south-west corner), perhaps accounting 
for where the major roads are, as Greg suggests.   (We also have the 
option of telling model users that if there is not enough resolution in 
the model's output for a particular region, they could divide the 
polygon *before* our model runs, to allow other parts of our modelling 
system to take effect.)

I used a "view" for an interim table, instead of just an alias.  Is this

going to slow things down?  The whole thing is quite slow on my two 
example parcels; I'm not sure how it's going to scale to 139000 parcels 
or 2.5 million parcels.  I'll add some more indices and then try it on 
the 139000 parcel set.

Finally, I don't know how to loop the last bit of code.  I could do it 
in java, python or a DOS Batch file, but if anyone can point me to how 
to do it in SQL, I would be much obliged.  Below are the commands that 
need to be executed in the first 2 runs through the final loop.  I can 
put the numbers "1" and "2" into a table easily enough, but for some 
reason I'm having a mental block about how to nest that into SQL.

UPDATE my_parcel_shards
    SET pseudoParcel = 1
    FROM bestPositionOption b
WHERE my_parcel_shards.parcel_no = b.parcel_no
    AND my_parcel_shards.shard_hpos <= b.shard_hpos
    AND my_parcel_shards.shard_vpos <= b.shard_vpos
    AND pseudoParcel IS NULL;

UPDATE my_parcel_shards
    SET pseudoParcel = 2
    FROM bestPositionOption b
WHERE my_parcel_shards.parcel_no = b.parcel_no
    AND my_parcel_shards.shard_hpos <= b.shard_hpos
    AND my_parcel_shards.shard_vpos <= b.shard_vpos
    AND pseudoParcel IS NULL;

Thanks so much,

--
John Abraham
jabraham at ucalgary.ca
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.