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

Murat Beyhan beyhan at deprem.gov.tr
Fri Mar 2 07:42:25 EST 2012





Dear Chaitanya,

I see what you explain first I convert lat lon data to string then I
concatenate them to string again. But I would like to ask
I have already add data to mysql table as follows is this also correct
because when I browse table it seems it store data in geometry field
but mapserver gives error
Unable to identify source field 'fault' for geometry.
any way
I will try to convert data first then I can return back you.
Regards...

ps.

ogrinfo
MYSQL:dynamic,user=root,password=mysql_123,host=localhost,port=3306,tables=geom
INFO: Open of
`MYSQL:dynamic,user=root,password=mysql_123,host=localhost,port=3306,tables=geom'
      using driver `MySQL' successful.
1: geom (None)




it say none in geom
may be first I have to do what you say
thanks
Murat


 On Fri, 2012-03-02 at 18:05 +0530, Chaitanya kumar CH wrote:
> Murat,
> 
> Put aside linestrings and geometries. First you need to have a wkt
> representation of your line segment. Your MySQL query is returning a
> number. That should be an indicator that your query was wrong. You
> were using the wrong operator. You need to convert the latitude and
> longitude values to string and concatenate the them with the other
> strings to make up the wkt of linestring geometries. Otherwise, MySQL
> is just adding the string and decimal values mathematically.
> 
> Use the CONVERT function as described in the website:
> http://www.geeksengine.com/database/single-row-functions/conversion-functions.php
> 
> On Fri, Mar 2, 2012 at 5:38 PM, Murat Beyhan <beyhan at deprem.gov.tr>
> wrote:
>         Chaitanya,
>         
>         I could not do this using sql sorry.
>         
>         But I have an another idea is it good way to store these data
>         as a
>         spatial database in MySql.
>         I try this but when I insert data to mysql table
>         I have problem .
>         I have created a table
>         as follows
>         
>         eq_id varchar(14)
>         fault linestring
>         
>         then I will insert data (if I success how to do that)
>         then I will use this table from mapserver.
>          is this proper way.
>         Or how to declare geom column as string at sql statement as
>         follows
>         
>          create view lineview as select eq_id, "LINESTRING(" + lon1 +
>         " " + lat1
>          + "," + lon2 + " " + lat2 + ")" as geom from sfault
>         
>         
>         
>         please give your idea...
>         
>         
>         
>         On Thu, 2012-03-01 at 14:40 +0530, Chaitanya kumar CH wrote:
>         > Murat,
>         >
>         >
>         > It's not working. the geom field should not be 'Real'. It
>         should be
>         > 'String'. Try something else other than the '+' operator in
>         the SQL
>         > syntax to create the geom field in the view.
>         >
>         > On Thu, Mar 1, 2012 at 12:48 PM, Murat Beyhan
>         <beyhan at deprem.gov.tr>
>         > wrote:
>         >         Chaitanya,
>         >
>         >         I have had a progress about VRT data access
>         >         but I afraid something wrong in mapserver .map file.
>         >
>         >         here is the test.vrt file and I use in map file with
>         >         connection ogr
>         >         functionality. :
>         >
>         >         <OGRVRTDataSource>
>         >         <OGRVRTLayer name="dynamic">
>         >
>         <SrcDataSource>MYSQL:dynamic,user=root,password=mysql_passwd,host=localhost,port=3306,tables=lineview</SrcDataSource>
>         >         <SrcSql>select eq_id, geom from lineview</SrcSql>
>         >         <GeometryType>wkbLineString</GeometryType>
>         >         <GeometryField encoding="WKT" field="geom"/>
>         >
>         >         </OGRVRTLayer>
>         >         </OGRVRTDataSource>
>         >
>         >         then the command
>         >
>         >
>         >         [murat at localhost stations]$ ogrinfo -ro -al test.vrt
>         >         INFO: Open of `test.vrt'
>         >              using driver `VRT' successful.
>         >
>         >
>         >         Layer name: dynamic
>         >         Geometry: Line String
>         >         Feature Count: 2
>         >         Layer SRS WKT:
>         >         (unknown)
>         >
>         >         eq_id: String (42.0)
>         >         geom: Real (23.0)
>         >         OGRFeature(dynamic):0
>         >          eq_id (String) = 20111023101120
>         >          geom (Real) =                     164
>         >
>         >         OGRFeature(dynamic):1
>         >          eq_id (String) = 20100308023229
>         >          geom (Real) =                     163
>         >
>         >
>         >
>         >         it works now but mapserver gives an error as
>         follows:
>         >
>         >         Server error!
>         >         The server encountered an internal error and was
>         unable to
>         >         complete your
>         >         request.
>         >
>         >         Error message:
>         >         Premature end of script headers: mapserv
>         >
>         >         If you think this is a server error, please contact
>         the
>         >         webmaster.
>         >
>         >
>         >         Error 500
>         >         10.14.0.67
>         >         Apache/2.2.14 (Mandriva Linux/PREFORK-1.6mdv2010.0)
>         >
>         >
>         >
>         >         I think I will solve it with your help.
>         >
>         >         Many thanks
>         >
>         >         Murat
>         >
>         >
>         >         On Thu, 2012-03-01 at 09:22 +0530, Chaitanya kumar
>         CH wrote:
>         >         > 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
>         >         >
>         >
>         >         > --
>         >         > This message has been scanned for viruses and
>         >         > dangerous content by MailScanner, and is
>         >         > believed to be clean.
>         >
>         >
>         >
>         >         Murat BEYHAN
>         >
>         >         Jeofizik Y.Müh.
>         >         T.C. Başbakanlık
>         >         Afet ve Acil Durum Yönetimi Başkanlığı
>         >         Deprem Dairesi Başkanlığı
>         >         Eskişehir Yolu 12. Km.
>         >         Lodumlu/ANKARA
>         >         Tel: 312 2872680-1556
>         >         email:beyhan at deprem.gov.tr
>         >
>         >
>         >
>         >
>         >         --
>         >         This message has been scanned for viruses and
>         >         dangerous content by MailScanner, and is
>         >         believed to be clean.
>         >
>         >
>         >
>         >
>         >
>         >
>         > --
>         > Best regards,
>         > Chaitanya kumar CH.
>         >
>         > +91-9494447584
>         > 17.2416N 80.1426E
>         >
>         >
>         > --
>         > This message has been scanned for viruses and
>         > dangerous content by MailScanner, and is
>         > believed to be clean.
>         
>         
>         
>         Murat BEYHAN
>         
>         Jeofizik Y.Müh.
>         T.C. Başbakanlık
>         Afet ve Acil Durum Yönetimi Başkanlığı
>         Deprem Dairesi Başkanlığı
>         Eskişehir Yolu 12. Km.
>         Lodumlu/ANKARA
>         Tel: 312 2872680-1556
>         email:beyhan at deprem.gov.tr
>         
>         
>         
>         
>         --
>         This message has been scanned for viruses and
>         dangerous content by MailScanner, and is
>         believed to be clean.
>         
>         
> 
> 
> 
> -- 
> Best regards,
> Chaitanya kumar CH.
> 
> +91-9494447584
> 17.2416N 80.1426E
> 
> -- 
> This message has been scanned for viruses and 
> dangerous content by MailScanner, and is 
> believed to be clean.



Murat BEYHAN 

Jeofizik Y.Müh.
T.C. Başbakanlık
Afet ve Acil Durum Yönetimi Başkanlığı
Deprem Dairesi Başkanlığı
Eskişehir Yolu 12. Km.
Lodumlu/ANKARA
Tel: 312 2872680-1556
email:beyhan at deprem.gov.tr




-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.



More information about the gdal-dev mailing list