[postgis-users] Getting dimensions of polygon sides
Obe, Regina
robe.dnd at cityofboston.gov
Thu Jun 12 05:32:58 PDT 2008
Martin,
I'm curious what your imaginery SPLIT BY would look like applied to this
problem. It doesn't seem like a straightforward split by to. I'm
thinking if we can come up with enough use cases and imaginery examples,
we can come up with a more or less ideal implementation of SPLIT BY.
Bruce,
Thanks for the suggestion you mentioned about putting the WHERE in the
FROM. Even though I couldn't do that it did get me thinking that my
series was too long. The max points I have is about 200 for the set I
am looking at and I think about 600 for the whole parcel table.
Reducing my series to 500 slashed the time by almost half.
On another note. I've been trying to improve on this and ST_Simplify
seems to do the trick more or less if I pick a very low simplification
parameter. Although in rare cases it messes up the colinearity which
isn't too big a deal. Would ST_PreserveTopology solve that minor issue
(I don't have the new postgis installed on this box so can't test that).
Anyrate - my simplified version
INSERT INTO assessing.parcdimstime_2008(pid, pid_year, the_geom)
SELECT b.pid, b.pid_year, ST_MakeLine(ST_PointN(the_boundary,n),
ST_PointN(the_boundary, n + 1)) As the_side
FROM (SELECT pid, pid_year, the_boundary,
ST_NumPoints(the_boundary) As nump
FROM (SELECT ap.parcelid as pid, 2008 as pid_year,
ST_Simplify(ST_Boundary(ap.the_geom), 1) As
the_boundary
FROM dnd.rems_survey ap ) p) As b
CROSS JOIN generate_series(1, 500) n
WHERE n < b.nump;
And look at the attached results. Its for the most part perfect.
Seems like once you solve one problem it creates others.
Anyrate my new problem: This whole exercise just made me realize they
must have put these polygons together with the constituent linework of
the edges. Because if I look at the abutting parcels those extra dims
represent sides of those smaller parcels. My simplify solution works
great if you are just looking at one parcel, but when you look at
abutters - it shall we say, looks crowded. So my thought is to put the
position of my length annotation inside each parcel it represents.
I'm lost how to do that. So basically jimmy the ST_Centroid(the_geom)
of my above such that it always sits inside the parcel boundary.
Thanks,
Regina
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Martin Davis
Sent: Wednesday, June 11, 2008 5:47 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Getting dimensions of polygon sides
Sigh - yet another use for a SPLIT BY clause!
Paragon Corporation wrote:
> Bruce,
>
> Unfortunately I don't think I can move that into the CROSS JOIN
because the
> generate_series would then be dependent on the other table. If
PostgreSQL
> had a CROSS APPLY similar to SQL Server 2005, then I could implement
what
> you are saying and it probably would be faster. Anyrate I think my
1000 is
> too high so I could probably improve speed a fair amount by reducing
that to
> max numpoints of any of my parcels.
>
> For 5000 parcels the insert took I think about 60 secs on my modest
server
> and generated about 19000 records. I had originally planned to do all
> 200,000 parcels and when I started thinking 200,000 X 10 would
generate over
> 2 million records (then I forgot I had condos in there that I should
have
> excluded so really only 1 million) , I decided to just scale down to
what I
> actually needed for this particular project.
>
> Thanks,
> Regina
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Bruce
> Rindahl
> Sent: Wednesday, June 11, 2008 4:25 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Getting dimensions of polygon sides
>
> Regina
> After looking closely at your code I think you are doing exactly the
same as
> I suggested. I had to add the intermediate steps because my method
needed
> to find the line (distance) between 2 arbitrary points on the polygon
where
> you are always looking at two adjacent ones.
> The only thing I see is your last line. Will changing :
>
> FROM dnd.rems_survey ap ) p) As b
> CROSS JOIN generate_series(1, 1000) n
> WHERE n < b.nump;
>
> to:
>
> FROM dnd.rems_survey ap ) p) As b
> CROSS JOIN generate_series(1, b.nump -
1) n;
>
>
> be any faster? It should avoid a WHERE test in each iteration.
>
> Bruce
>
> Obe, Regina wrote:
>
>> Bruce,
>>
>> Thanks for the below. I think it will take me a while to digest what
>> you are doing. I think I learned a couple of things off the bat.
>>
>> 1) I may need the Right Hand Rule call since I was assuming things
>> were already ordered correctly and my simple spot check seems to
>> suggest that it is but you never know.
>>
>> 2) I'm confused between ST_Boundary and ST_ExteriorRing and when to
>> use one over the other. I assume ST_Boundary takes into
consideration
>> holes where as ST_ExteriorRing just gives you the outer ring. Now
I'm
>> thinking about it I probably should replace my ST_Boundary with
>> ST_ExteriorRing.
>>
>> In case anyone is curious. Attached is a snapshot of what I get when
>> I use my simple 2 point assumption. It works in most cases but in
>> others where they used more than 2 points to describe each corner, I
>> get extra measures.
>> Not the dimok is my ideal case and the dimalmost okay - see how I
have
>> an extra measure.
>>
>> You think Simplifying before I extract would fix that.
>>
>> My final query and updates look like this - seems to run fairly fast
>> for the 5000 parcel list I care about.
>>
>> INSERT INTO assessing.parcdimstime_2008(pid, pid_year, the_geom)
>> SELECT b.pid, b.pid_year, ST_MakeLine(ST_PointN(the_boundary,n),
>> ST_PointN(the_boundary, n + 1)) As the_side
>> FROM (SELECT pid, pid_year, the_boundary,
>> ST_NumPoints(the_boundary) As nump
>> FROM (SELECT ap.parcelid as pid, 2008 as pid_year,
>> ST_Boundary(ap.the_geom) As
>> the_boundary
>> FROM dnd.rems_survey ap ) p) As b
>> CROSS JOIN generate_series(1, 1000) n
>> WHERE n < b.nump;
>>
>>
>> UPDATE assessing.parcdimstime_2008 SET side_length =
>> CAST(ST_Length(the_geom) As numeric(8,2));
>>
>>
>>
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
--
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
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.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: beforesimplify.png
Type: image/png
Size: 15745 bytes
Desc: beforesimplify.png
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080612/42520bec/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: aftersimplify.png
Type: image/png
Size: 11961 bytes
Desc: aftersimplify.png
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080612/42520bec/attachment-0001.png>
More information about the postgis-users
mailing list