Using JOIN for querying

[Fawcett, David]

Greg, 

To me, the query is returning what I would expect from a database query.


Formatting the results as you want them seems more like a templating or
data formatting issue, not a query issue.  

If you are using a javascript or custom interface, you should be able to
format the results the way that you want.  

Does anyone who has done more with MapServer templates know how/if this
could be done with template magic?  

David.

-----Original Message-----
From: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU] On
Behalf Of Greg Luker
Sent: Sunday, September 03, 2006 6:49 PM
To: MAPSERVER-USERS at LISTS.UMN.EDU
Subject: Re: [UMN_MAPSERVER-USERS] Using JOIN for querying


Hi David,

(sorry for the long post)

David Fawcett said:
>I haven't been following this thread too closely, but are you sure that

>you want to use the unmaintained MyGIS option, or do you really want to

>use OGR with MySQL support enabled?  I would go with OGR unless there 
>is something in MyGIS that doesn't exist in OGR.

Thanks for your suggestions. I agree that I would be better off with OGR
- and I have:

**************
LAYER
  NAME "pts"
  TYPE POINT
  STATUS OFF
  CONNECTIONTYPE OGR
  CONNECTION '<OGRVRTDataSource>
   <OGRVRTLayer name="exact">

<SrcDataSource>MYSQL:sites,user=user1,password=*****,host=11.109.9.10,po
rt=3
306,tables=feature;site</SrcDataSource>
    <SrcSQL>SELECT site_id,
     site_name,
     site_easting_other,
     site_northing_other,
     feature_id,
     feature_name
     FROM site s
     JOIN feature f ON s.site_id = f.feature_site_id
    </SrcSQL>
    <GeometryType>wkbPoint</GeometryType>
    <GeometryField encoding="PointFromColumns" x="site_easting_other"
y="site_northing_other"/>
   </OGRVRTLayer>
  </OGRVRTDataSource>'

  DATA "exact"
  TOLERANCE 5
  PROJECTION
   "proj=utm"
   "south"
   "zone=56"
  END #projection

  CLASS
   NAME "Test Site - pts"
   TEMPLATE "pquery.html"
   SYMBOL "Dot"
   SIZE 5
   COLOR 0 0 255
  END #class
  HEADER "pquery_header.html"
  FOOTER "pquery_footer.html"
END #layer
*********************

Using a map mode of nquery, the template pquery.html produces:

Site Name         Feature
site1_name       feature1_name
site1_name       feature2_name
site1_name       feature3_name
site2_name       feature4_name
site2_name       feature5_name
site3_name       feature6_name
site3_name       feature7_name
...

but what I want is:

site1_name
          feature1_name
          feature2_name
          feature3_name
site2_name
          feature4_name
          feature5_name
site3_name
          feature6_name
          feature7_name
...

I can't seem to get this using OGR (I don't know the correct syntax) so
I thought I'd try to get what I wanted by using MyGIS because somebody
helped me with the syntax.

>Here is Frank's page for OGR/MySQL: 
>http://ogr.maptools.org/drv_mysql.html

>Here is a description from Howard's blog: 
>http://hobu.biz/index_html/mysql_ogr

Thankyou for these URLs. I have been to them both and, while they are
helpful, I still don't know the right OGR syntax for what I'm trying to
do. Could you help with the correct OGR syntax?

>Also, look at the HowTos, there are a few items on MySQL.

Could you please give a URL for these HowTos? I have Google-searched and
asked on this list for weeks, but unfortunately I have not solved my
"OGR/MySQL/query/multiple tables/join" question, though I have received
related help from several on this list (thanks).

Thanks,
Greg Luker.