Guy, What spatial ref sys is your data? Is it longlat or meter/ft based?
As I think Steve Woodbridge mentioned, I think you'll want to take a look at the linear referencing functions documented here http://postgis.refractions.net/documentation/manual-svn/ch07.html#Linear_Ref erencing In particular -- ST_Line_Interpolate_point http://postgis.refractions.net/documentation/manual-svn/ST_Line_Interpolate_ Point.html So if you know the overlap is 200m in and the network component is 400m and you are using a non-long lat coordinate system, then your fraction will be 0.50 Hope that helps, Regina -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Guy Thomas Sent: Monday, February 16, 2009 2:31 AM To: [email protected] Subject: [postgis-users] Re: Which postGIS functions to use? Sorry if I wasn't clear enough. I know the length of the overlapping piece of the quay with the network component. I want to calculate the end coordinate (a point) of the quay on the network component. quay x--------------------y network component a--------------k-----------------b The overlap is, let's say, 200m. I want to calculate point k, its coordinates, on the network component starting from point a, k being 200m away from a. [email protected] wrote: > Send postgis-users mailing list submissions to > [email protected] > > To subscribe or unsubscribe via the World Wide Web, visit > http://postgis.refractions.net/mailman/listinfo/postgis-users > or, via email, send a message with subject or body 'help' to > [email protected] > > You can reach the person managing the list at > [email protected] > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of postgis-users digest..." > > > Today's Topics: > > 1. Why Multiply? Why not? ( Mohamad Solikin (via Multiply) ) > 2. RE: Which postGIS functions to use? (Paragon Corporation) > 3. Re: Which postGIS functions to use? (Stephen Woodbridge) > 4. PostGIS functions inside triggers (Eduin Yezid Carrillo Vega) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Sun, 15 Feb 2009 1:43:34 -0500 > From: " Mohamad Solikin (via Multiply) " <[email protected]> > Subject: [postgis-users] Why Multiply? Why not? > To: [email protected] > Message-ID: <[email protected]> > Content-Type: text/plain; charset=utf-8 > > > Check out my Multiply site > > I set up a Multiply site with my pictures, videos and blog and I want > to add you as my friend so you can see it. First, you need to join > Multiply! Once you join, you can also create your own site and share > anything you want, with anyone you want. > > Here's the link: > http://multiply.com/si/5iVP2eBXx98C1NJENpUzPg > > Thanks, > Mohamad > > > > > > > > Stop e-mails, view our privacy policy, or report abuse: > http://multiply.com/bl/5iVP2eBXx98C1NJENpUzPg > We haven't added your email address to any lists, nor will we share it > with anyone at any time. > Copyright 2009 Multiply Inc. > 6001 Park of Commerce, Boca Raton, FL 33487, USA > > > > ------------------------------ > > Message: 2 > Date: Sun, 15 Feb 2009 05:39:28 -0500 > From: "Paragon Corporation" <[email protected]> > Subject: RE: [postgis-users] Which postGIS functions to use? > To: "'PostGIS Users Discussion'" > <[email protected]> > Message-ID: <f96d34a9696b465f9fe7d73585115...@b> > Content-Type: text/plain; charset="us-ascii" > > I don't quite understand your question. > > Are you trying to get the length of the intersection of the 2? So > that part of the network component that falls within the quay? > > In that case it would be something of the form > > SELECT ST_Length(ST_Intersection(netcomponent.the_geom, > quay.the_geom)) > > Which assumes your netcomponent is defined as a LINESTRING (you can > use ST_MakeLine > http://postgis.refractions.net/documentation/manual-svn/ST_MakeLine.ht > ml > if you have 2 points and no line) > > And quay is some sort of polygon. > > Hope that helps, > Regina > > -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf Of > [email protected] > Sent: Saturday, February 14, 2009 11:31 AM > To: [email protected] > Cc: [email protected] > Subject: [postgis-users] Which postGIS functions to use? > > I have a "network component" (= part of a waterway) defined by 2 > coordinates. > I know how to calculate the length of that component. > > However, another component, let's say a quay, overlaps with the > component. I know how far it overlaps (in meters) with the component > from the beginning coordinate and I want to calculate the end > coordinate of the quay (that should be on the network component). > > How do I do this? > > Thank you > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > ------------------------------ > > Message: 3 > Date: Sun, 15 Feb 2009 11:15:40 -0500 > From: Stephen Woodbridge <[email protected]> > Subject: Re: [postgis-users] Which postGIS functions to use? > To: PostGIS Users Discussion <[email protected]> > Message-ID: <[email protected]> > Content-Type: text/plain; charset=ISO-8859-1; format=flowed > > I'm not sure if I understand you question correctly, but here is a > solution for my understanding. > > So you have a network component that is partially overlapped by the > quay and you know the length along the network component where the > overlap starts or stops. You can extract the part of the network > component that overlap with ST_Intersection or ST_Difference as Regina > suggests. You can also do this using the linear referencing functions > which might be > faster: > > From start of netcomponent to your distance of intersection: > > SELECT ST_line_substring(netcomponent.the_geom, 0.0, > dist_meters/ST_length_spheroid(netcomponent.the_geom,spheroid)) > > From your distance of intersection to the end of the netcomponent: > > SELECT ST_line_substring(netcomponent.the_geom, > dist_meters/ST_length_spheroid(netcomponent.the_geom,spheroid), 1.0) > > If you just want the point of intersection with the quay then this > should do it: > > SELECT ST_locate_along_measure(netcomponent.the_geom, > dist_meters/ST_length_spheroid(netcomponent.the_geom,spheroid)) > > All these functions assume your geometry is in units degrees and you > will need to define an appropriate value for spheroid like: > > SPHEROID[<NAME>,<SEMI-MAJOR AXIS>,<INVERSE FLATTENING>] > > for example: > > 'SPHEROID["GRS_1980",6378137,298.257222101]' > > -Steve > > Paragon Corporation wrote: >> I don't quite understand your question. >> >> Are you trying to get the length of the intersection of the 2? So >> that part of the network component that falls within the quay? >> >> In that case it would be something of the form >> >> SELECT ST_Length(ST_Intersection(netcomponent.the_geom, >> quay.the_geom)) >> >> Which assumes your netcomponent is defined as a LINESTRING (you can >> use ST_MakeLine >> http://postgis.refractions.net/documentation/manual-svn/ST_MakeLine.h >> tml >> if you have 2 points and no line) >> >> And quay is some sort of polygon. >> >> Hope that helps, >> Regina >> >> -----Original Message----- >> From: [email protected] >> [mailto:[email protected]] On Behalf Of >> [email protected] >> Sent: Saturday, February 14, 2009 11:31 AM >> To: [email protected] >> Cc: [email protected] >> Subject: [postgis-users] Which postGIS functions to use? >> >> I have a "network component" (= part of a waterway) defined by 2 >> coordinates. >> I know how to calculate the length of that component. >> >> However, another component, let's say a quay, overlaps with the >> component. I know how far it overlaps (in meters) with the component >> from the beginning coordinate and I want to calculate the end >> coordinate of the quay (that should be on the network component). >> >> How do I do this? >> >> Thank you >> >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > ------------------------------ > > Message: 4 > Date: Sun, 15 Feb 2009 12:16:26 -0500 > From: Eduin Yezid Carrillo Vega <[email protected]> > Subject: [postgis-users] PostGIS functions inside triggers > To: [email protected] > Message-ID: > <[email protected]> > Content-Type: text/plain; charset=ISO-8859-1 > > Maybe I miss something, but ST_Contains isn't available inside this > trigger. It returns empty values trying to get which polygon in table2 > contains a recently inserted point on table1. table1.the_geom value is > filled correctly but table1.table2_id value is null. Tried ST_Within > and ST_Intersects also. > > > CREATE TABLE table1 > ( > table1_id serial NOT NULL, > lon character varying(30), > lat character varying(30), > table2_id character varying(200), > the_geom geometry, > CONSTRAINT table1_pkey PRIMARY KEY (table1_id), > CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2), > CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = > 'POINT'::text OR the_geom IS NULL), > CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 4326) > ) > WITH (OIDS=FALSE); > > CREATE TRIGGER table1_change > AFTER INSERT OR UPDATE OR DELETE > ON table1 > FOR EACH ROW > EXECUTE PROCEDURE table1_change(); > > CREATE TABLE table2 > ( > gid serial NOT NULL, > table2_id character varying, > the_geom geometry, > CONSTRAINT table1_pkey PRIMARY KEY (table2_id), > CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2), > CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = > 'MULTIPOLYGON'::text OR the_geom IS NULL), > CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 4326) > ) > WITH (OIDS=FALSE); > > > CREATE OR REPLACE FUNCTION table1_change() > RETURNS trigger AS > $BODY$ > DECLARE > the_point geometry; > a_row table2%ROWTYPE; > BEGIN > IF tg_op = 'INSERT' THEN > > SELECT ST_GeomFromText('POINT(' || new.lat || ' ' || new.lon || > ')',4326) INTO the_point; > > SELECT * FROM table2 WHERE ST_Contains(table2.the_geom, > the_point) INTO a_row; > > UPDATE table1 SET > table2_id = a_row.table2_id, > the_geom = the_point > WHERE table1_id=new.table1_id; > > END IF; > RETURN new; > END > $BODY$ > LANGUAGE 'plpgsql' VOLATILE > COST 100; > ALTER FUNCTION table1_change() OWNER TO postgres; > > > Triggered with: > INSERT INTO table1(table1_id, lon, lat) VALUES > (999,-73.138583333333,7.0854783333333); > > > Eduin Yesid Carrillo Vega > Ingeniero Civil, Esp. Sistemas de Información Geográfica Bogotá D.C., > Colombia > > > ------------------------------ > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > End of postgis-users Digest, Vol 76, Issue 15 > ********************************************* > -- Guy Thomas [email protected] fks bvba - Formal and Knowledge Systems http://www.fks.be/ Stationsstraat 108 Tel: ++32-(0)11-21 49 11 B-3570 ALKEN Fax: ++32-(0)11-22 04 19 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
