R: Re: [gdal-dev] create kml file using MySQL database or shape file

Wed May 4 09:03:16 EDT 2011

Hi Murat,

try to explicitly add g (the geometric column) in the select clause:

SELECT g, koy,lat,lng,sisint from deneme2

otherwise ogr2ogr won't be able to see it when creating KML layer.

All the best

Sig

----Messaggio originale----
Da: beyhan at deprem.gov.tr
Data: 04/05/2011 14.25
A: "sigfrido at tiscali.it"<sigfrido at tiscali.it>
Cc: "gdal-dev at lists.osgeo.org"<gdal-dev at lists.osgeo.org>
Ogg: Re: R: Re: [gdal-dev] create kml file using MySQL database or 
shape file

Dear Sigfrido

As you told me I have added a geometry column to my database as g

and 

I executed following sql statement
This is the first time for me to make spatial database table . So I
think first of all I have to learn more about Spatial database concept
in MySQL.

any way

UPDATE deneme2
SET g = GeomFromText(
CONCAT('POINT(', CAST(lat AS CHARACTER(20)),
' ', CAST(lng AS CHARACTER(20)),')')
) ;

than I used 

 ogr2ogr -f KML sisint.kml
MySQL:'afetler,host=localhost,user=root,password=mysql_123,port=3306,
tables=deneme2' -sql "SELECT koy,lat,lng,sisint from deneme2"

ogrinfo -ro sisint.kml

The problem is there is no layer in KML file I think.

ERROR 4: No layers in KML file: sisint.kml.
FAILURE:
Unable to open datasource `sisint.kml' with the following drivers.
  -> ESRI Shapefile
  -> MapInfo File
  -> UK .NTF
  -> SDTS
  -> TIGER
  -> S57
  -> DGN
  -> VRT
  -> REC
  -> Memory
  -> BNA
  -> CSV
  -> GML
  -> GPX
  -> KML
  -> GeoJSON
  -> Interlis 1
  -> Interlis 2
  -> GMT
  -> SQLite
  -> ODBC
  -> PGeo
  -> OGDI
  -> MySQL
  -> PCIDSK
  -> XPlane
  -> AVCBin
  -> AVCE00
  -> DXF
  -> Geoconcept
  -> GeoRSS
  -> GPSTrackMaker
  -> VFK

so I couldn't understand what is wrong or what I have to do first.

Really I need to create such a point or polygon data extracted from
MySQL database to KML format. Please help me

Murat

On Wed, 2011-05-04 at 12:08 +0200, sigfrido at tiscali.it wrote:
> Hi Murat,
> 
> I'm not so much into KML, but I think that maybe the 
> problem is with your SQL: you are not including any geometry column 
in 
> the select instruction. If you open the KML file with a text 
editor, 
> you can see if the points are being exported and with which 
> coordinates.
> 
> Does the MySQL table have a geometric column? Or you just 
> have the lon/lat coordinates?
> If you don't have geometry you have first 
> to add it to the MySQL table, update it with the point geometry 
created 
> from lon/lat, and then export the points in KML.
> 
> Hope this helps
> 
> Sig
> 
> 
> ----Messaggio originale----
> Da: beyhan at deprem.gov.tr
> Data: 04/05/2011 
> 9.17
> A: "Luca Sigfrido Percich"<sigfrido at tiscali.it>
> Cc: "gdal-
> dev at lists.osgeo.org"<gdal-dev at lists.osgeo.org>
> Ogg: Re: [gdal-dev] 
> create kml file using MySQL database or shape file
> 
> Dear Sigfrido,
> 
> 
> Thank you for your help,
> I corrected the script and it works but when I 
> open it with google-earth
> points doesn't seem on the map I sent 
> snapshot of the google-earth.I
> think problem is latitude and longitude 
> could not read by the
> google-earth.
> So How can I fix it...
> 
> 
> 
> Regards. 
> 
> 
> ogr2ogr -f KML sisint.kml
> MySQL:'afetler,host=localhost,user=root,
> password=mysql_123,port=3306,tables=sisint' -sql "SELECT koy, 
> longitude, latitude , sisint from sisint"
> 
> koy means village name.
> 
> sisint menans seismic intensity value of the village
> 
> Murat 
> 
> 
> On Tue, 
> 2011-05-03 at 17:33 +0200, Luca Sigfrido Percich wrote: 
> > Hi Murat,
> > 
> 
> > Il giorno mar, 03/05/2011 alle 16.57 +0300, Murat Beyhan ha 
scritto:
> 
> > > ogr2ogr -f KML simgmt.kml simgmt.shp -dsco NameField=RegionName
> > > 
> this can create a file but strangely data collected at one point.
> > 
> > 
> Probably it is a Spatial Reference System related problem: you 
should
> > 
> specify the source shapefile SRS. Have a look at the KML file and 
see 
> if
> > the points have different coordinates, and see what SRS has been
> > 
> declared for it. Have a look at the -a_srs, -t_srs and -s_srs 
options 
> of
> > ogr2ogr.
> > 
> > 
> > > ogr2ogr -f KML sisint.kml MySQL 
> :'host=localhost user=root
> > > password=mysql_pswd port=3306 
> dbname=afetler tables=sisint' -sql "SELECT
> > > latitude,longitude,
> sisint from sisint"
> > 
> > Try with no space between MySQL and :, and 
> specifying the database name
> > before any other option:
> > 
> > ogr2ogr -f 
> KML sisint.kml
> > MySQL:'afetler,host=localhost,user=root,
> password=mysql_pswd,port=3306,tables=sisint' -sql "SELECT latitude,
> longitude,sisint from sisint"
> > 
> > All the best
> > 
> > Sig
> > 
> 
> 
> 
> -- 
> 
> Murat BEYHAN
> Geophysical Engineer, M.Sc.
> Prime Ministry
> Disaster and 
> Emergency Management Presidency
> Earthquake Department
> Address: 
> Eskisehir 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.
> 
> 
> 
> 
> 
> 
> Non temiamo alcun confronto: Tiscali ha l'Adsl più veloce d'Italia!
> 
> Risparmia con Tutto Incluso Light: Voce + Adsl 20 mega a soli 17,95 
€ al mese per 12 mesi.
> 
> http://abbonati.tiscali.it/telefono-
adsl/prodotti/tc/tuttoincluso_light/?WT.mc_id=01fw
> 

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.

SCARICA TISCALI WIPHONE: parla e invia SMS gratis dal tuo cellulare.
http://wiphone.tiscali.it