Intersections
Gregory S. Williamson
gsw at GLOBEXPLORER.COM
Thu Oct 14 15:53:06 PDT 2004
Not sure i like your approach as LIKE is an expensive operator, but this works for me:
gex_vector=# select gid,name from texas_rivers where AsText(the_geom) like '%4274417%';
gid | name
-----+--------
3 | Brazos
(1 row)
Use the AsText to get the WKT representation of the geometry column ...
HTH,
Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From: Eric Katherman [mailto:vaiod at BERLOY.COM]
Sent: Thu 10/14/2004 3:47 PM
To: MAPSERVER-USERS at LISTS.UMN.EDU
Cc:
Subject: Re: [UMN_MAPSERVER-USERS] Intersections
The GEOMETRY fields contain data in this format:
SRID=-1;MULTILINESTRING((496255.079 867186.461,496255.641 867285.9,496257.078
867536.466))
All with varying lengths obviously.
I wanted to just do comparisons with that data but Postgres doesn't like a
simple LIKE '%mynumber%'...
I will see if I can find any info on that on the web.
Thanks for your prompt help Bob, I appreciate it.
Eric
> Eric Katherman wrote:
> >My streets table contains the following fields which contain basically
> >everything but the to and from name.
> >
> >gid INT4
> >objectid INT8
> >length NUMERIC (e.g. 208.98332212)
> >add1_l INT8 (e.g. 27656)
> >add1_r INT8 (e.g. 27657)
> >add2_l INT8 (e.g. 27720)
> >add2_r INT8 (e.g. 27721)
> >date_ VARCHAR
> >source INT4
> >map INT4
> >community VARCHAR
> >type VARCHAR (e.g. Rd)
> >name VARCHAR (the street name)
> >the_geom GEOMETRY
> >
> >Unfortunately my table doesn't have as much detail as your's appears to,
> >although with what I see, in theroy I should be able to create an "snode"
> >table from where any point in the "the_geom" table matches another and
> > then get the names from them right? If Central and 5th intersect then
> > there would be a record for both Central and 5th that should have that
> > corresponding point in it.
>
> that's the idea. then you let the user figure out which record they
> want to use. We even do some generalizing after the select by grouping
> all location less than 150 feet from each other into a single point.
> This works well for those spots that have two raodways crossing each
> other in both direction, wher typically you would get back four
> different intersection points.
>
> >The problem that I had when trying to test a select statement using
> > the_geom table returned this:
> >
> >SQL>select name from streets where the_geom LIKE '475151.296';
> >ERROR:operator does not exist: geometry ~~ "unknown"
> >HINT:No operator matches the given name and argument type(s). You may need
> > to add explicit type casts.
>
> Are the numbers stored as Ascii or actual numbers? If the GEOMETRY is
> anything like ORACLE, it's a bit harder to get at it directly with a SQL
> select. You may need a procedure of sort to get at the actual values.
>
> bobb
>
> >Eric
> >
> >>Eric Katherman wrote:
> >>>The linetypes are LINE and I just started playing with postgis and have
> >>>successfully been able to draw the centerlines from the DB if that helps
> >>>at all. I am not sure if the line segments are city blocks or not, I got
> >>>the data from my county.
> >>>
> >>>Here are two lines from ogrinfo.
> >>
> >>This is very similar to what I started with for our centerline file.
> >>The way I handle the intersections is to create a new SQL table from the
> >>Segment data. In my dataset though, I have the Names of the street as
> >>a text field. It also has the FROM and TO street names in it as well.
> >>With these three piece of data a nd the endpoint coordinates, one can
> >>assemble a new TABLE with a self join SQL request that builds a point
> >>database with the name of each street name that ends at each
> >>corrosponding line segment or SuperNODE (SNODE). Note: this will get
> >>all street names at every intersectionn, not just those that cross each
> >>other. Notice further down, that DALE, COMO and FRONT all intersect each
> >>other at the same intersection.
> >>
> >>
> >>The end results look something like this?
> >>
> >>SQL> desc snodes;
> >> Name Null? Type
> >> ----------------------------------------- --------
> >>----------------------------
> >> ID CHAR(9)
> >> X NUMBER(6)
> >> Y NUMBER(6)
> >> INTERS CHAR(100)
> >>
> >>SQL> select * from snodes where rownum < 5;
> >>
> >>ID X Y
> >>--------- ---------- ----------
> >>INTERS
> >>-------------------------------------------------------------------------
> >>-- ----- 301000001 546326 171797
> >>COMO AV and HUNTING VALLEY RD
> >>
> >>
> >>301000003 546801 171503
> >>COMO AV and EUSTIS ST
> >>
> >>
> >>301000004 548524 171520
> >>
> >>ID X Y
> >>--------- ---------- ----------
> >>INTERS
> >>-------------------------------------------------------------------------
> >>-- ----- BRANSTON ST
> >>
> >>
> >>301000005 548959 171546
> >>DUDLEY AV and GRANTHAM ST
> >>
> >>
> >>
> >>SQL> select * from snodes where inters like '%COMO%' and inters like
> >>'%FRONT%';
> >>
> >>ID X Y
> >>--------- ---------- ----------
> >>INTERS
> >>-------------------------------------------------------------------------
> >>-- ----- 312000057 564421 165392
> >>COMO PL and FRONT AV
> >>
> >>
> >>901500906 566512 165392
> >>COMO AV and FRONT AV and N DALE ST
> >>
> >>
> >>
> >>----------------------------------------------------
> >>
> >>The create statement (for Oracle, your's will be different I'm sure)
> >>would look something like this:
> >>
> >>create view snode as
> >> select fnode_ snode, streetname, cityrigt, cityleft, x1, y1, z1
> >> from tlg_county
> >>union
> >> select tnode_, streetname, cityrigt, cityleft, x2, y2, z2
> >> from tlg_county
> >>
> >>Your data will obviously be different, but hopefully this helps you in
> >>the right direction.
> >>
> >>Once you have a SNODE file then you can just add it as another layer
> >>behind MapServ that can be selected via the StreetNames or via an ID
> >>from some other process, we use a PERL CGI for this against the
> >>database, and then zoom to the spot with MapServ, but it could all just
> >>be done inside of MapServ too.
> >>
> >>---------------------------------------------------
> >>
> >>The TLG_COUNTY table referenced above looks like this:
> >>
> >>SQL> desc tlg_county
> >> Name Null? Type
> >> ----------------------------------------------------- --------
> >>------------------------------------
> >> GEOM
> >>MDSYS.SDO_GEOMETRY
> >> FNODE_
> >> FLOAT(126) TNODE_
> >> FLOAT(126) LPOLY_
> >> FLOAT(126) RPOLY_
> >> FLOAT(126) LENGTH
> >> FLOAT(126) TLGRDS_
> >> FLOAT(126) TLGRDS_ID
> >> FLOAT(126) L_F_ADD
> >> FLOAT(126) R_F_ADD
> >> FLOAT(126) L_T_ADD
> >> FLOAT(126) R_T_ADD
> >> FLOAT(126) STREETALL
> >>VARCHAR2(35) F_CLASS
> >>VARCHAR2(6) ALT_NAM1
> >>VARCHAR2(36) ALT_NAM2
> >>VARCHAR2(36) CENCTY_L
> >>FLOAT(126) CENMUN_L
> >>FLOAT(126) CENCTY_R
> >>FLOAT(126) CENMUN_R
> >>FLOAT(126) ZIP5_L
> >>FLOAT(126) ZIP5_R
> >>FLOAT(126) CITYLEFT
> >>VARCHAR2(20) CITYRIGT
> >>VARCHAR2(20) PREDIR
> >>VARCHAR2(2) STREETNAME
> >>VARCHAR2(20) TYPE
> >>VARCHAR2(8) SUFDIR
> >>VARCHAR2(2) CTYLABRV
> >>VARCHAR2(3) CTYRABRV
> >>VARCHAR2(3) SPEED_LIM
> >>FLOAT(126) ONEWAY
> >>FLOAT(126) TLGID
> >>FLOAT(126) X1
> >>FLOAT(126) Y1
> >>FLOAT(126) Z1
> >>FLOAT(126) X2
> >>FLOAT(126) Y2
> >>FLOAT(126) Z2
> >>FLOAT(126)
> >>
> >>
> >>bobb
> >>
> >>>OGRFeature(Streets):4
> >>> OBJECTID (Integer) = 13
> >>> LENGTH (Real) = 449.92674416700
> >>> ADD1_L (Integer) = 0
> >>> ADD1_R (Integer) = 0
> >>> ADD2_L (Integer) = 0
> >>> ADD2_R (Integer) = 0
> >>> DATE_ (Integer) = 19970705
> >>> SOURCE (Integer) = 2
> >>> MAP (Integer) = 8
> >>> COMMUNITY (String) = Maricopa County
> >>> TYPE (String) = (null)
> >>> NAME (String) = Apartment
> >>> LINESTRING (459403.220 1075050.365,459434.741 1074948.799,459520.438
> >>>1074768.288,459596.252 1074646.142)
> >>>
> >>>OGRFeature(Streets):5
> >>> OBJECTID (Integer) = 14
> >>> LENGTH (Real) = 787.59510637000
> >>> ADD1_L (Integer) = 0
> >>> ADD1_R (Integer) = 0
> >>> ADD2_L (Integer) = 0
> >>> ADD2_R (Integer) = 0
> >>> DATE_ (Integer) = 19970705
> >>> SOURCE (Integer) = 2
> >>> MAP (Integer) = 8
> >>> COMMUNITY (String) = Maricopa County
> >>> TYPE (String) = (null)
> >>> NAME (String) = Apartment
> >>> LINESTRING (459382.095 1075094.728,459287.050 1075056.376,459285.226
> >>>1074952.446,459294.343 1074888.628,459478.501 1074564
> >>>.073,459596.252 1074646.142)
> >>>
> >>>>What does your Street centerline data look like now?
> >>>>
> >>>>Is it a seperate line segment per city block or polylines or ???
> >>>>
> >>>>bobb
> >>>>
> >>>>Eric Katherman wrote:
> >>>>>Can someone please give me some idea or a sample on how one would
> >>>>> begin to find an intersection of two lines, streets in my case. Info
> >>>>> on finding intersecting lines with other data besides streets could
> >>>>> be helpful as well. I ultimately want to give a user the option to
> >>>>> enter two street names and then zoom to that intersection. Any
> >>>>> suggestions or examples?
> >>>>>
> >>>>>Eric
More information about the MapServer-users
mailing list