[postgis-users] Getting dimensions of polygon sides

Randall, Eric ERandall at eriecountygov.org
Wed Jun 11 12:07:59 PDT 2008


oops... below

 "create table parcel_dims (pin text, segment numeric(11,3), length float);"

 should be  

"create table parcel_dims (pin text, segment integer, length numeric(11,3));

...

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of
Randall, Eric
Sent: Wednesday, June 11, 2008 2:57 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Getting dimensions of polygon sides


Hi Regina,

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.


Eric



CREATE OR REPLACE FUNCTION taxparcel_to_segments() RETURNS integer AS $$

	DECLARE
		
        getrow CURSOR FOR SELECT * FROM taxparcel;
        
        tp_row taxparcel%ROWTYPE;
        		
        vsegment integer;
	  maxrows integer;
        maxpoints integer;
        i integer;
        j integer;
	  rowcount integer;

	BEGIN

	/*  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);    */

	TRUNCATE parcel_dims;

	SELECT INTO maxrows count(*)FROM  taxparcel;

	OPEN getrow;

	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),
			ST_makeline(ST_pointn(ST_boundary(tp_row.geom),i),ST_pointn(boundary(tp_row.geom),j))  );

		END LOOP;

	END LOOP;


	RETURN(rowcount);
		
	END;
	

$$ language plpgsql


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of Obe,
Regina
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
fields.

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
the_boundary
			FROM assessing.parceltime
			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);

Thanks,
Regina

-----------------------------------------
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
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



More information about the postgis-users mailing list