[gdal-dev] drawing line using OGR and mysql in mapserver

[Chaitanya kumar CH]

Murat,

You should check MySQL's SQL syntax to concatenate strings and decimals to
produce a string.

On Wednesday, February 29, 2012, Murat Beyhan <beyhan at deprem.gov.tr> wrote:
> Chaitanya,
>
> here is the structure of table on database
>
> sfault :
>
> eq_id varchar(14)
> lat1  decimal(7,4)
> lon1  decimal(7,4)
> lat2  decimal(7,4)
> lon2  decimal(7,4)
>
> lineview :
>
>
> eq_id varchar(14)
> geom  double
>
>
> it seams geom as double
> I thing it should be geometry isn't it?
>
> may be  I have to change structure of lineview by changing following
> Roberts' suggestion to create view on database
>
>  create view lineview as select eq_id, "LINESTRING(" + lon1 + " " + lat1
>  + "," + lon2 + " " + lat2 + ")" as geom from sfault
>
>
> but I'm not sure for the line.
>
> VTR for points, I have tested and works well
>
>
> but for line still could not achieved yet, please give me another idea.
>
> here is test.vrt file
>
> <OGRVRTDataSource>
> <OGRVRTLayer name="sta">
>
<SrcDataSource>MYSQL:stations,user=root,password=mysql_passwd,host=localhost,port=3306,tables=sta</SrcDataSource>
> <SrcSQL>SELECT sta_id as name,type,latitude,longitude from sta where
> type like "%SMACH%"</SrcSQL>
> <GeometryType>wkbPoint</GeometryType>
> <GeometryField encoding="PointFromColumns" x="longitude" y="latitude"/>
> </OGRVRTLayer>
> </OGRVRTDataSource>
>
> and following code gives successful result as you see
>
>
> ogrinfo -ro -al test.vrt
>
> INFO: Open of `test.vrt' using driver `VRT' successful.
>
> Layer name: sta
> Geometry: Point
> Feature Count: 12
> Layer SRS WKT:
> (unknown)
> name: String (12.0)
> type: String (24.0)
> latitude: Real (5.4)
> longitude: Real (5.4)
> OGRFeature(sta):0
>  name (String) = 0617
>  type (String) = SMACH
>  latitude (Real) = 40.4569
>  longitude (Real) = 32.6319
>  POINT (32.631900000000002 40.456899999999997)
>
> OGRFeature(sta):1
>  name (String) = 0618
>  type (String) = SMACH
>  latitude (Real) = 40.4798
>  longitude (Real) = 32.4555
>  POINT (32.4555 40.479799999999997)
>
> .
> .
> .
> .
>
>
> As you see this works well.
>
> I hope you can catch point what is wrong or lack of on my works....
>
>
> Thanks again
>
>
>
>
> On Tue, 2012-02-28 at 18:08 +0530, Chaitanya kumar CH wrote:
>> Murat,
>>
>>
>> I see that your view,  lineview, doesn't give the expected geometry
>> type with ogrinfo. It is showing the datatype as Real. Check if the
>> datatype of geom is actually string/text.
>>
>> On Tue, Feb 28, 2012 at 12:49 PM, Murat Beyhan <beyhan at deprem.gov.tr>
>> wrote:
>>         Chaitanya,
>>
>>
>>         Map file which is try to draw line as follows
>>         are there any problem on the code
>>
>>          LAYER
>>          NAME sfault1
>>          CONNECTIONTYPE OGR
>>          CONNECTION '<OGRVRTDataSource>
>>          <OGRVRTLayer name="lineview">
>>
>>
<SrcDataSource>MYSQL:dynamic,user=root,password=mysqlpasswd,host=localhost,port=3306,tables=lineview</SrcDataSource>
>>          <SrcLayer>lineview</SrcLayer>
>>          <GeometryType>wkbLineString</GeometryType>
>>          <GeometryField encoding="WKT" field="geom"/>
>>          <LayerSRS>WGS84</LayerSRS>
>>          <FID>eq_id</FID>
>>          </OGRVRTLayer>
>>          </OGRVRTDataSource>'
>>          DATA sfault1
>>          STATUS on
>>          TYPE line
>>          CLASS
>>          NAME "Source Fault"
>>          SYMBOL "cline"
>>           SIZE 3
>>          COLOR  0 0 255
>>          END
>>          END
>>
>>
>>         ogrinfo
>>
MYSQL:dynamic,user=root,password=mysqlpasswd,host=localhost,port=3306
>>         sfault -summary
>>         INFO: Open of
>>
`MYSQL:dynamic,user=root,password=mysqlpasswd,host=localhost,port=3306'
>>              using driver `MySQL' successful.
>>
>>         Layer name: sfault
>>         Geometry: None
>>         Feature Count: 1
>>         Layer SRS WKT:
>>         (unknown)
>>         eq_id: String (14.0)
>>         lat1: Real (7.4)
>>         lon1: Real (7.4)
>>         lat2: Real (7.4)
>>         lon2: Real (7.4)
>>
>>         **************linewiew created by following sql
>>         statement:*************
>>
>>
>>          create view lineview as select eq_id, "LINESTRING(" + lon1 +
>>         " " + lat1
>>          + "," + lon2 + " " + lat2 + ")" as geom from sfault
>>
>>
>>         then
>>
>>         [murat at localhost ~]$ ogrinfo
>>
MYSQL:dynamic,user=root,password=mysqlpasswd,host=localhost,port=3306
>>         lineview -summary
>>         INFO: Open of
>>
`MYSQL:dynamic,user=root,password=mysqlpasswd,host=localhost,port=3306'
>>              using driver `MySQL' successful.
>>
>>         Layer name: lineview
>>         Geometry: None
>>         Feature Count: 1
>>         Layer SRS WKT:
>>         (unknown)
>>         eq_id: String (14.0)
>>         geom: Real (0.0)
>>
>>
>>

-- 
Best regards,
Chaitanya kumar CH.

+91-9494447584
17.2416N 80.1426E
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/gdal-dev/attachments/20120301/fa38539d/attachment.html