Intersections
Eric Katherman
vaiod at BERLOY.COM
Thu Oct 14 15:35:52 PDT 2004
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.
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.
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