[postgis-users] Getting dimensions of polygon sides
lr at pcorp.us
Wed Jun 11 14:19:37 PDT 2008
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.
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Bruce
Sent: Wednesday, June 11, 2008 4:25 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Getting dimensions of polygon sides
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;
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.
Obe, Regina wrote:
> 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
> 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
> 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
More information about the postgis-users