Using JOIN for querying

[Greg Luker]

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,port=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.