[postgis-users] points to multi-linestring or multipart linestring

[Obe, Regina]

Oops had a typo in my code - should be -anyrate I didn't test so could
have more errors

SELECT gps.track_id, 
	ST_Collect(ST_MakeLine(CASE WHEN class1=1 THEN gps.the_geom ELSE
NULL END),
		ST_MakeLine(CASE WHEN class2=1 THEN gps.the_geom ELSE
NULL END))  As newgeom
	FROM (SELECT track_id,ST_SetSRID(ST_MakePoint(long,lat),4326) As
the_geom, class1, class2
			FROM raw_data ORDER BY dtime, id) As gps
	GROUP BY gps.track_id 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe,
Regina
Sent: Friday, October 17, 2008 7:07 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] points to multi-linestring or multipart
linestring

Michael,
I'm not sure what a multi part line feature class is in ESRI.  I assume
probably MULTILINESTRING.
 
Are you trying to get one geometry out per track_id or 2 records.
 
If 1 record - then that would be a LINESTRING
If 2 records then that would be a MULTILINESTRING
 
If I understand you correctly - I think  you want to use the ST_MakeLine
aggregate function in conjunction with the non-aggregate ST_Collect
 
http://postgis.refractions.net/documentation/manual-svn/ST_MakeLine.html

http://postgis.refractions.net/documentation/manual-svn/ST_Collect.html
 
So I'm going to assume you are trying to do 2.  So there would be a
little twist.  It would look something like the below.

The below will generate a multilinestring for each track_id where the
multiline is composed of 2 linestrings - one for class1 and one for
class2
 
SELECT gps.track_id, 
	ST_Collect(ST_MakeLine(CASE WHEN class1=1 THEN gps.the_geom ELSE
NULL END),
		ST_MakeLine(CASE WHEN class2=1 THEN gps.the_geom ELSE
NULL))  As newgeom
	FROM (SELECT track_id,ST_SetSRID(ST_MakePoint(long,lat),4326) As
the_geom, class1, class2
			FROM raw_data ORDER BY dtime, id) As gps
	GROUP BY gps.track_id

Hope that helps,
Regina


________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Michael Diener
Sent: Friday, October 17, 2008 6:35 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] points to multi-linestring or multipart
linestring


Hi,

How do I create a multipart line feature class known in the ESRI world
in Postgresql/postgis ?
What feature type is it equivalent to...umm... multilinestring or
linestring or...xxxx  in Postgresql PostGIS?

I have a points geom table of coordinates with attributes.  Using these
attributes I want to generate a linestring or multilinestring where ever
the attribute is active with a 1.    So I need some SQL statement here
to do this somehow.

below is an example of how the raw data looks  the attributes are class1
and class2 

table name: raw_data  (type: point  srid 4326)

id;track_id;Lat;Long;Alt;DTime;SoG;DOP;class1;class2
1,35,46.596805,14.27998,392.4,2008-10-10 06:48:24,9.11184,8.4,0,1
2,35,46.5969333333333,14.2799983333333,390.2,2008-10-10
06:48:29,9.76004,8.4,0,1
3,35,46.597085,14.2800533333333,388.2,2008-10-10
06:48:38,8.01916,8.4,1,0
4,35,46.5972883333333,14.280115,387.8,2008-10-10
06:48:46,8.48216,7.8,1,1
5,35,46.5973966666667,14.280215,386.9,2008-10-10 06:48:53,9.8156,7.8,1,1
6,35,46.5975683333333,14.2803133333333,386.3,2008-10-10
06:49:01,8.48216,7.8,0,0
7,35,46.597785,14.2803816666667,387.3,2008-10-10
06:49:08,8.77848,7.8,1,1
8,35,46.597955,14.2804516666667,386.3,2008-10-10
06:49:14,11.03792,7.8,1,1
9,35,46.5981683333333,14.2805466666667,386.9,2008-10-10
06:49:21,10.2786,7.8,0,1

class1      0,0,1,1,1,0,1,1,0
class2      1,1,0,1,1,0,1,1,1

For class1    I should have 2 new line segments
For class2    I should have 3 new line segments

thanks for any help   rusty on the SQL

michael

m.diener at gomogi.com


<http://www.gomogi.com> 
-----------------------------------------
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