[mapserver-users] Extracting X,Y from MULTIPOINT data

[Andy Colson]

> -----Original Message-----
> From: Andy Colson [mailto:andy at squeakycode.net] 
> Sent: Monday, August 10, 2009 8:52 AM
> To: Suman Chatterjee
> Cc: mapserver-users at lists.osgeo.org
> Subject: Re: [mapserver-users] Extracting X,Y from MULTIPOINT data
> 
> Suman Chatterjee wrote:
>> Hi all,
>>
>> I have some multipoint data in postgis that I got from street 
>> intersections. But I am not able to extract the X,Y coordinates from
> the 
>> MULTIPOINT data.
>>
>> Is there any function in postgis by which I can extract the X,Y point 
>> data from them?
>>
>> Please help!
>>
>>  
>>
>> Thanks,
>>
>> Suman
>>
> 
> Yes.  Are you looking for all points?  Just the first?  Some subset? 
> How do you want to see it?
> 
> Did you try ST_AsText(col)?
> 
> There is also ST_NumPoints(geometry) and ST_PointN(geometry,integer) if 
> you want to look at them individually.  An example of what youd like to 
> see, or what you are trying to do might be helpful.
> 
> More functions here:
> 
> http://postgis.refractions.net/documentation/manual-1.3/ch06.html
> 
> -Andy
> 

Suman Chatterjee wrote:
 > Hi Andy,
 > Thanks for your help. Actually, I am having both point and multipoint
 > data in the same table which I got through road intersections by
 > applying ST_intersection.
 > I am using this function for extracting the coordinates from the POINT
 > data.
 >
 > Select distinct
 > x(geometryn(multi((st_intersection)),1)),y(geometryn(multi((st_intersect
 > ion)),1)),c,d from (select
 > ST_intersection(a.the_geom,b.the_geom),a.fname as c,b.fname as d from
 > roads1 as a, roads1 as b where a.fname<>b.fname and
 > ST_intersects(a.the_geom,b.the_geom) as drfe
 >
 > But I am not getting the X,Y for the multipoint data. I require X,Y for
 > all the points within the multipoint set.
 > I can extract the multipoints from the whole by
 >
 > Create table ex_intersects as (select
 > ST_intersection(a.the_geom,b.the_geom),a.fname as c,b.fname as d from
 > roads1 as a, roads1 as b where a.fname<>b.fname and
 > ST_intersects(a.the_geom,b.the_geom)
 >
 > Select st_intersection from ex_intersects where
 > geometrytype(ST_intersection)='MULTIPOINT'
 >
 > I tried with St_astext but it is giving the set of multipoints. But I
 > want the X,Y for all the points within the multipoint geometry.
 >
 > How to do that?
 > Thanks
 > Suman
 >

Wow, you are a lot deeper into postgis than I am, not sure if I'll be 
any help.

Question: ST_intersection(a.the_geom,b.the_geom) returns a multipoint?

Then x(geometryn(multi((st_intersection)),1)) would return just the x 
coord of the first point?

but you want the x coord of all the points?  (yes, y too, I'm just 
trying to break it down to understand)

-Andy