[postgis-users] Getting dimensions of polygon sides
ERandall at eriecountygov.org
Wed Jun 11 11:57:24 PDT 2008
A very non-elegant try on my parcels.
This just ends up making lines out of every two point segment of a parcel poly and IDs them in order.
CREATE OR REPLACE FUNCTION taxparcel_to_segments() RETURNS integer AS $$
getrow CURSOR FOR SELECT * FROM taxparcel;
/* select DropGeometryColumn('','parcel_dims','geom');
drop table parcel_dims;
create table parcel_dims (pin text, segment numeric(11,3), length float);
select addgeometrycolumn('public','parcel_dims','geom',2271,'LINESTRING',2); */
SELECT INTO maxrows count(*)FROM taxparcel;
rowcount := 0;
i := 1;
WHILE rowcount < maxrows LOOP -- might want to limit this to 100 to test
FETCH getrow into tp_row;
rowcount := rowcount + 1;
select into maxpoints ST_npoints(tp_row.geom);
FOR i in 1 .. maxpoints - 1 LOOP
vsegment := i;
j := i + 1;
INSERT INTO parcel_dims VALUES
( tp_row.taxpin, vsegment, ST_length(ST_makeline(ST_pointn(ST_boundary(tp_row.geom),i),ST_pointn(ST_boundary (tp_row.geom),j)))::numeric(11,3),
$$ language plpgsql
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of Obe,
Sent: Wednesday, June 11, 2008 11:02 AM
To: PostGIS Users Discussion
Subject: [postgis-users] Getting dimensions of polygon sides
I'm trying to similuate ArcGIS annotations. I guess ArcPad doesn't
support ArcGIS annotations according to what I have been told but can
support line strings and so forth.
Here is the the problem. I have a set of parcel polygon geometries.
I'm going to assume that each lines side is composed of 2 points and no
polygon has more than 1000 sides. I need to create a table that has a
separate row for each side with the length of that side as an attribute
I stupidly thought I could take the boundary and then figure out the
length of the boundary forgetting that this just gives me the perimeter.
So my second thought was that if I reconstitute points of the boundary
grouping 2 at a time - that would do the trick.
My query is still running so haven't looked to see what the final result
is. I'm wondering if someone has done something similar and if they
have an easier way.
Below is the query I am testing right now.
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 pid, pid_year, ST_Boundary(the_geom) As
WHERE pid_year = 2008) p) As b
CROSS JOIN generate_series(1, 1000) n
WHERE n < b.nump;
UPDATE assessing.parcdimstime_2008 SET sd_length = ST_Length(the_geom);
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.
postgis-users mailing list
postgis-users at postgis.refractions.net
More information about the postgis-users