Svinjica Svinjica - 25 days ago 15
PHP Question

Getting geojson linestring from MySQL geometry WKT data

I'm trying to get GeoJSON linestring format using awesome PHP-Database-GeoJSON library from here https://github.com/bmcbride/PHP-Database-GeoJSON but for now without any luck

I was successful with calling simple points from MySQL table using that library but with lines and polygons the story is a bit different.

First, I created table with geometry field, something like this:

-- phpMyAdmin SQL Dump
-- version 4.4.6
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 20, 2016 at 10:12 PM
-- Server version: 5.6.24
-- PHP Version: 5.6.8

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `web_gis`
--

-- --------------------------------------------------------

--
-- Table structure for table `wkt_linija`
--

DROP TABLE IF EXISTS `wkt_linija`;
CREATE TABLE IF NOT EXISTS `wkt_linija` (
`id` int(11) NOT NULL,
`oznaka` varchar(50) NOT NULL,
`opis` varchar(200) NOT NULL,
`sirina` varchar(200) NOT NULL,
`podloga` varchar(200) NOT NULL,
`mo` varchar(200) NOT NULL,
`geometry` geometry NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `wkt_linija`
--

INSERT INTO `wkt_linija` (`id`, `oznaka`, `opis`, `Å¡irina`, `podloga`, `mo`, `geometry`) VALUES
(1, 'K-8', 'D206 - Vulica - Kraljevec - Horvati - Kordej - S1', '5 m', 'Asfalt-makadam', 'Kostel', '\0\0\0\0\0\0\0¿\0\0\0§5l¹r/@##LêG@eëŸëÑr/@,†‰©G@E¤úâèr/@*éöŠG@Ç4sùr/@õYd\rG@}VHÉs/@·-G@-©`›\Zs/@ÜdÏG@Bß%s/@}*†G@®ñ„/s/@óÅ,ÞG@Ùª³ã7s/@w!™cÿG@PÝAs/@zOöüG@‘t·ÀMs/@Ê.›ÖúG@W…_e]s/@w;yùG@e­‰tts/@š¢ýöG@Ñ„»”s/@S`ùåôG@±å¡´s/@»÷‡óG@ÉSÔÏs/@Ê5@DòG@Ê„Sñs/@<zòG@¥HÖHt/@Ï\n±ÏñG@8Wt/@«ôÚðG@Þôz(t/@äò=îG@׎¬/9t/@‚áoéG@•‡\0Kt/@ûq\0äG@kª†Xt/@n”TTßG@%-šmjt/@ÒÃ|‡ÛG@a =‰t/@\Zñ‡ÕG@‚~bÉt/@bnåÈG@’Oƒøt/@Ñ»oÙ¿G@r¶¡ãu/@F+BºG@ðèð9u/@õO‘¶G@õmeVu/@íójB´G@¬íæ_gu/@’''™²G@žº’îou/@›CØ°G@è1Bou/@Ž­G@8è’ju/@ýEŒªG@|¯Yu/@#H|L¦G@V8ÏHu/@ò†M¢G@¹¡_„0u/@ÑÞ„žG@ø*È>/u/@ä]*RžG@s«®\0\ru/@› ššG@Ãbát/@m«NÈ—G@[÷¾t/@)hNè“G@…!E‹t/@‘FÕŽG@[$ïtbt/@ŠuŠG@8#dJt/@c€ÌD‡G@윔Î.t/@žZ.†G@4/bt/@qx›\Z†G@¶´·¯òs/@iŒ¶&†G@×Í>¼Õs/@Ãís³†G@ÃW‡@µs/@RqÈG@B-Ä…¡s/@B^µŠG@Ò3©‘s/@9Tþ5‰G@å»Øáˆs/@†ÍÀ†G@£ã…s/@S»ÌƒG@j)jm†s/@(E瘀G@üÊqцs/@\0{\0zG@ÂÄas/@Ë/c¸wG@¬¹…÷‘s/@d1oLsG@’€Ms/@c¹pG@©Æ+̤s/@àv¬lG@} ±s/@°{§,hG@»Bi,¿s/@ÆÇúœcG@sbæ1Ïs/@JJ‹#`G@ø;·rÑs/@''>v¦_G@§`]Ös/@òc±]G@\Z¯žØs/@ZÂ`Ë\\G@/à/?Ýs/@µeòWG@ïú®ùës/@³¾â¿RG@®žéñs/@M.ö±NG@pÿ^òs/@ ÍaNG@<Éhýs/@rÙ–LG@¸*Bt t/@<ŸˆIG@Þù)t/@ËGò>DG@ûÄj(t/@A©ÂK@G@¡›ãÕ-t/@:íB=G@o,5t/@Ÿ²@;G@a¬t*Bt/@ ¤‘8G@!zKt/@L¬ž6G@lj}Ut/@Ы¼¹4G@}Ö¼\\wt/@›É¤û.G@JI&4œt/@ÖD¨(G@2Jæš±t/@‘&¡#G@âq^³±t/@8Ûe›#G@“º»MÇt/@ï  G@‘£ßt/@™(FÏG@ª8W÷t/@¿¼¹UG@íQñGu/@º3æGG@²Þ2Ýu/@_¶L¶G@âR€u/@j|æ’G@''‚Öau/@½-ÞùG@ö¹­)u/@óèP‡G@-x1Ju/@È3¤G@˜Ôî%u/@Û$ÍG@H‘ðu/@<˜G@UþBu/@=JPýG@¾ß"u/@K›tãúG@Oºw¤#u/@†‘ËÁúG@HO]N,u/@“;­GùG@eæß?u/@B_LöG@ÌvmÝVu/@áó×óG@˜§Ev[u/@¥2ÄðG@@TÏl_u/@â+qgíG@ɽ\Zeu/@[]a…çG@:ø )gu/@g-áG@é’du[u/@>6wMÞG@úmNƒUu/@­V¦óÙG@ˆøÆQu/@ÔKÿÕG@¥K?MQu/@–ÑKÒG@œu~Vu/@¹Ã[õÎG@­È­ø]u/@ ÛñoÌG@D¦Åhu/@ÙŠ˜,ÊG@¤DÕ£wu/@Yá¿ÈG@+\Z‰u/@a¦­ÇG@lV3\r¤u/@׬œ?ÆG@ €ì¸u/@R½JJÅG@3}qaÂu/@èjûÄG@¢U§½Èu/@Í-)ÂG@j(Íu/@è.P¿G@`ûfåÑu/@ÂÁ¸F»G@þ¨²Öu/@œ™? ¸G@–Çû’Þu/@‰Œû]¶G@ÏÀéu/@3%´G@LhÝ]üu/@ß6$±G@]‘ó v/@ ©Zð®G@0s+v/@¥Éæ¬G@¤]¶v/@ŽòרG@˜.ôæ\Zv/@®÷—Š¤G@h v/@"á\0M—G@Ðitv/@ׄ½’G@ðüv/@Ý5­BŽG@µ3 \nv/@é¨þ‹G@ZTLv/@øP{‰ˆG@r;$P÷u/@ÏV’>„G@ëàrYëu/@˜wÒ9G@à°y!Ýu/@ðÿMy~G@ÐfÃu/@g"òázG@3H¹Œ­u/@kU¸HwG@䉽;šu/@‰ôó™rG@ÿQJu/@ÜâroG@¶‰Š)‰u/@F>#klG@PC´ú€u/@û]ŸªhG@ˆÚzu/@0EóLfG@Á¼©pu/@òÔ¦UdG@€]"X[u/@\0³''0bG@gÖæˆMu/@Ç>\\`G@Ð\n|–Fu/@!¤……^G@''Ú9ôDu/@×Oi\\G@íµ PFu/@DmÅ^YG@¨®ÍHu/@¡žîUG@_¶WéJu/@Ål RG@£y¯OMu/@e¡ÆMG@™3¯äKu/@FpmHG@,šlAHu/@bä;‰DG@^J+îCu/@¾äPBG@æˆO\\=u/@y^ßþ?G@Î2¿.u/@‘šþ=G@œ—7Ru/@Óœ79G@˜¡q‹u/@+ê©ñ4G@ÖŒ„u/@³ì%–1G@÷fáÔ u/@%ó\Z|.G@|´zºu/@&í“´+G@"¬+u/@OÉó\\)G@Þ_µ™u/@lW™%G@#§G[)u/@õŽ(''G@$t/“4u/@EIroG@q/;^>u/@l”ÂG@¤¿²¯Lu/@³ê‰G@ß|Smau/@^GnG@Ê »Çuu/@ÃÖ¤ÃG@”1î‡u/@èÐ…\ZG@7úHd•u/@/U€G@>bo¿¡u/@¢ôeöG@uß®u/@&\nP-\0G@ï‹}r·u/@Tñ-ºüG@sU~ø¹u/@œ^Ì6úG@²eóºu/@걓\n÷G@~‹W)¸u/@bÍôG@Y0ø±u/@Üa*ÔòG@ug#k¨u/@müñG@INñ®šu/@i(q‹ñG@7ŸVˆu/@Š"ÈðG@Vÿ•ru/@MðFwïG@Éúeäbu/@i¶ÖíG@Fà;Uu/@ZÛ:ÐëG@ˆ+ìOu/@‘D4éG@t.Mu/@z 6æG@ä€3sHu/@èY;ãG@ÒŸng>u/@x9„9àG@"‚ƒX1u/@3òïÝG@JT2u/@ïv\n–ÛG@7¯u/@%F\0ØG@—ÍH0ôt/@‰œìÄÔG@');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `wkt_linija`
--
ALTER TABLE `wkt_linija`
ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `wkt_linija`
--
ALTER TABLE `wkt_linija`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


Here is picture. I don't know why but when I exported table I got strange symbols in geometry row, something like this "/@‰œìÄÔG@". Is it coz I selected GeomFromText function when creating geometry field ?

Picture example

The real question is this. When I'm using this block of code to extract data from MySQL and convert to GeoJSON:

<?php

include_once('geoPHP/geoPHP.inc');

$conn = new PDO('mysql:host=localhost;dbname=web_gis;charset=utf8','neven','gis',array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));

$sql = 'SELECT *, geometry AS wkt FROM wkt_linija';

$rs = $conn->query($sql);
if (!$rs) {
echo 'An SQL error occured.\n';
exit;
}

$geojson = array(
'type' => 'FeatureCollection',
'features' => array()
);

while ($row = $rs->fetch(PDO::FETCH_ASSOC)) {
$properties = $row;

unset($properties['wkt']);
unset($properties['geometry']);
$feature = array(
'type' => 'LineString',
'geometry' => json_decode(($row['wkt'])),
'properties' => $properties
);

array_push($geojson['features'], $feature);
}
header('Content-type: application/json');
echo json_encode($geojson, JSON_NUMERIC_CHECK);
$conn = NULL;
?>


Geometry field gets ignored and I get results without any geometry, like this:

{"type":"FeatureCollection","features":[{"type":"LineString","geometry":null,"properties":{"id":1,"oznaka":"K-8","opis":"D206 - Vulica - Kraljevec - Horvati - Kordej - S1","sirina":"5 m","podloga":"Asfalt-makadam","mo":"Kostel"}}]}


and should be getting something like this if I'm not mistaken:

{"type": "FeatureCollection","features": [
{ "type": "Feature", "properties": { "OBJECTID": 857, "OZNAKA": "K-8", "OPIS": "D206 - Vulica - Kraljevec - Horvati - Kordej - S1", "�IRINA": "5", "PODLOGE": "Asfalt-makadam", "DULJINA": 2.714530, "SHAPE_Leng": 2714.529879, "JLS": "Pregrada", "MO": "Kostel" }, "geometry": { "type": "LineString", "coordinates": [ [ 15.724070907944066, 46.188443457771676 ], [ 15.724257815619874, 46.188344185018963 ], [ 15.724433034034641, 46.188218470155633 ], [ 15.72455940264949, 46.188111948013024 ], [ 15.724699297027877, 46.187990209886379 ], [ 15.724812369870586, 46.187860416649634 ], [ 15.72489216542548, 46.187729597634622 ], [ 15.72497191850638, 46.187587520467694 ], [ 15.725035777742436, 46.18748135544859 ], [ 15.725105549827283, 46.187407292181923 ], [ 15.725202581781817, 46.187342477581822 ], [ 15.725321929846059, 46.187288459964229 ], [ 15.725497857862772, 46.187225059863707 ], [ 15.725739143267136, 46.18716120411468 ], [ 15.725983191502424, 46.187106791868665 ], [ 15.726194987431329, 46.187080890038104 ], [ 15.726450533317252, 46.187073593715894 ], [ 15.726602817685782, 46.187066995092771 ], [ 15.726732946011916, 46.187037820050442 ], [ 15.726868107327785, 46.186958068465223 ], [ 15.726998796285825, 46.18681143294458 ], [ 15.72713472037392, 46.186645560888834 ], [ 15.727237900023473, 46.186502972887737 ], [ 15.727374482221942, 46.186387000961659 ], [ 15.727609563629075, 46.186189834001382 ], [ 15.72809649660417, 46.185818387861914 ], [ 15.728458503155057, 46.185542277867746 ], [ 15.728743661394422, 46.185371657495338 ], [ 15.728957680163198, 46.185259021798828 ], [ 15.729172360784085, 46.185188581679405 ], [ 15.72930431074095, 46.185137873121803 ], [ 15.729369597827198, 46.185060721013734 ], [ 15.729363091545522, 46.184984028820601 ], [ 15.729328718981932, 46.184892184228353 ], [ 15.729199870961573, 46.184762535747971 ], [ 15.729071126927163, 46.184640592675251 ], [ 15.728885780977054, 46.184525116727102 ], [ 15.728876077589561, 46.184519072234053 ], [ 15.728614827463138, 46.184405640049697 ], [ 15.728282035417173, 46.184319532788187 ], [ 15.728019456787038, 46.184201277050597 ], [ 15.727625044660053, 46.184046453459025 ], [ 15.727313665573254, 46.183902676002639 ], [ 15.72713005952758, 46.183815574507001 ], [ 15.726919608727677, 46.183782381293177 ], [ 15.726687496419437, 46.183780027306859 ], [ 15.726460925269162, 46.183781470427512 ], [ 15.72624004618312, 46.183798247915114 ], [ 15.725992218514767, 46.183861189085874 ], [ 15.725841694066393, 46.183920515406967 ], [ 15.725715865521043, 46.183874844726638 ], [ 15.725653703409941, 46.18379975520206 ], [ 15.725630852584601, 46.18370970853416 ], [ 15.725634974679185, 46.183611977505564 ], [ 15.725637955799805, 46.183427810892681 ], [ 15.725685548243181, 46.183341072493555 ], [ 15.725723013948333, 46.183206133168625 ], [ 15.725809501193257, 46.183107700844715 ], [ 15.725866680473091, 46.183003917087035 ], [ 15.725960645833988, 46.182866651324161 ], [ 15.726067912911608, 46.182727453662253 ], [ 15.726190146802059, 46.182621424687184 ], [ 15.726207337253058, 46.182606513725027 ], [ 15.726244838952267, 46.182546780957537 ], [ 15.726262053423898, 46.182519361730286 ], [ 15.726297354311184, 46.182371391634128 ], [ 15.726409723863496, 46.182212815966885 ], [ 15.726455021424353, 46.182089085774898 ], [ 15.726458519588704, 46.1820795299138 ], [ 15.726542714259828, 46.182008813530857 ], [ 15.726634629323016, 46.181917731008497 ], [ 15.726792632784449, 46.181770199109074 ], [ 15.726870857759003, 46.181649656342877 ], [ 15.726912197145625, 46.181557050493623 ], [ 15.72696727853892, 46.181495749681858 ], [ 15.727067305319169, 46.181413846381595 ], [ 15.727138343041192, 46.181354299035405 ], [ 15.727214739777891, 46.181296555637914 ], [ 15.727473161746497, 46.181121306860597 ], [ 15.727754239724749, 46.180928261966685 ], [ 15.727917519198943, 46.180774825857164 ], [ 15.727918248442567, 46.180774140104802 ], [ 15.728083066145052, 46.180681243365932 ], [ 15.728268727778216, 46.180597218758699 ], [ 15.728449559847867, 46.180521693889652 ], [ 15.728548286636363, 46.180459010508869 ], [ 15.728575622997003, 46.180441653687687 ], [ 15.728626260838556, 46.180345881025303 ], [ 15.728632981715306, 46.180266602979351 ], [ 15.728631308037603, 46.180130877024475 ], [ 15.728609388865232, 46.180042767469388 ], [ 15.72863119640742, 46.179925578113505 ], [ 15.728675382393831, 46.179736203157688 ], [ 15.728754103415818, 46.179605518578782 ], [ 15.728781671000469, 46.179531509339164 ], [ 15.728787555327612, 46.17952749671808 ], [ 15.728853653803199, 46.179482421480451 ], [ 15.729002926254177, 46.179391428482766 ], [ 15.729178351986228, 46.179316475034064 ], [ 15.729213424685256, 46.17922260735417 ], [ 15.729243660267571, 46.179119997271997 ], [ 15.729286990802217, 46.178940460721343 ], [ 15.729302693271269, 46.178746820197198 ], [ 15.729213398482402, 46.178659136971405 ], [ 15.729168036769796, 46.178526359767922 ], [ 15.729134552992278, 46.178405644044375 ], [ 15.72913590810442, 46.178292729463621 ], [ 15.729175521649999, 46.178190870100885 ], [ 15.729232569898466, 46.178113930791596 ], [ 15.729312949540265, 46.178044867023068 ], [ 15.729428405555446, 46.177993744555984 ], [ 15.729561627668394, 46.177968698730346 ], [ 15.729767239109798, 46.177925063611546 ], [ 15.729926481857566, 46.177895819181359 ], [ 15.729998631576199, 46.177859065783025 ], [ 15.730047156025481, 46.177800306667997 ], [ 15.730080843602126, 46.177713416007506 ], [ 15.730117005177533, 46.177590217793764 ], [ 15.730153622997474, 46.177509337477403 ], [ 15.730213731036013, 46.177440402819293 ], [ 15.730294124893048, 46.177372576295973 ], [ 15.730441029813072, 46.177280928037128 ], [ 15.730544656814056, 46.177213711041304 ], [ 15.730607373995639, 46.177126634301466 ], [ 15.730649660998102, 46.177027590548377 ], [ 15.730673997218489, 46.176896404459839 ], [ 15.730686159036875, 46.176492333821685 ], [ 15.730678213040187, 46.176331131495026 ], [ 15.730644702457511, 46.17621644456424 ], [ 15.730591136985689, 46.176118015618904 ], [ 15.730532059952845, 46.176041779721288 ], [ 15.730402473852994, 46.175910779432904 ], [ 15.730311198495164, 46.175818660514039 ], [ 15.730202718834732, 46.175734675489252 ], [ 15.730003845739928, 46.175625079374008 ], [ 15.729839704148707, 46.175515260702774 ], [ 15.729692332160035, 46.175372356528051 ], [ 15.729618528196623, 46.175276053085071 ], [ 15.729562090073937, 46.175183670242902 ], [ 15.729499659065965, 46.175069167915318 ], [ 15.72944644429414, 46.1749969661563 ], [ 15.729370166749847, 46.174936968289231 ], [ 15.729212526507354, 46.174871463188538 ], [ 15.729107168367774, 46.174815683654337 ], [ 15.729054167405963, 46.17475956941076 ], [ 15.729041702329665, 46.17469517134213 ], [ 15.72905208551216, 46.174602362060369 ], [ 15.729071071151182, 46.174497439541717 ], [ 15.729087154347495, 46.174396528594606 ], [ 15.72910546319037, 46.174248531959883 ], [ 15.729094644915493, 46.17408531172007 ], [ 15.729066891200169, 46.17396655487461 ], [ 15.729033892425999, 46.173890151499549 ], [ 15.728983769139131, 46.173827990595733 ], [ 15.728872275277265, 46.173739313429117 ], [ 15.728754586492691, 46.173621131114167 ], [ 15.72865710985279, 46.173490722623988 ], [ 15.72860349855311, 46.173388260381579 ], [ 15.728590633883398, 46.173293603095125 ], [ 15.728612735251083, 46.173208782415074 ], [ 15.728646626318611, 46.173137301479635 ], [ 15.728726199522438, 46.173007798074508 ], [ 15.728831150528611, 46.172825712970656 ], [ 15.728916739981351, 46.172712260059178 ], [ 15.72899145576932, 46.172630617446742 ], [ 15.729100695199868, 46.172549311964211 ], [ 15.729258934441928, 46.172437431420562 ], [ 15.729414216596428, 46.172325568664313 ], [ 15.729546276642203, 46.172213855113853 ], [ 15.729655393513083, 46.172107398664927 ], [ 15.729749662729883, 46.171995925696379 ], [ 15.729846939429743, 46.171880401689393 ], [ 15.729915216279521, 46.171775124009088 ], [ 15.729934468671422, 46.171698426999882 ], [ 15.729941946170445, 46.171601602665604 ], [ 15.729920665680535, 46.17153322935043 ], [ 15.729873420226953, 46.171473046011414 ], [ 15.729800556251329, 46.171447282199416 ], [ 15.7296957654536, 46.171433859112604 ], [ 15.729553262542693, 46.171410576691585 ], [ 15.729389846247617, 46.171370420110726 ], [ 15.729270112465558, 46.171320761745136 ], [ 15.729164786156741, 46.171258953798954 ], [ 15.729123053626395, 46.17117657711163 ], [ 15.729104461899636, 46.171087984683425 ], [ 15.729068374669119, 46.170993474941326 ], [ 15.728991729978507, 46.170905294001329 ], [ 15.728892103245986, 46.170835384142627 ], [ 15.728746006764606, 46.170763735875759 ], [ 15.728573253271504, 46.170655965932646 ], [ 15.728425511252494, 46.170555701764052 ] ] } }
]
}


I don't know what I'm I missing. Is it something in .php block of code, or did I failed with creating table and selecting wrong function when creating geometry field, really don't know.

Thank you for any help or constructive criticism regarding question.

UPDATE



Even when including this block of code in .php file mentioned above ( copied from here https://github.com/phayes/geoPHP

<?php
include_once('geoPHP/geoPHP.inc');

function wkt_to_json($wkt) {
$geom = geoPHP::load($wkt,'wkt');
return $geom->out('json');
}
...


I get notice


Notice: Undefined offset: 1 in
C:\xampp\htdocs\PHP-GEOJSON\MySQL_LINIJA\geoPHP\lib\adapters\WKT.class.php
on line 23


and

fatal error


Fatal error: Call to a member function out() on null in
C:\xampp\htdocs\PHP-GEOJSON\MySQL_LINIJA\line.php on line 16

Answer

With some help (to be fair they were really helpful) I finally figure it out.

So, here is the deal. In geometry field (MySQL) I used GeomFromText function and for VALUE used format like this:

'LINESTRING(15.710658104182732 46.20239760250581,15.710776991330624 46.202150210442475,15.710822931962284 46.20192080948476,15.710831682524589 46.20165203949391,15.710829367910724 46.201478170096806,15.710778748807499 46.201345541255364,15.710749581923572 46.20098083884202,15.710768970490234 46.200904345256404,15.710812698319572 46.200819215538004,15.710877059895306 46.2007537567,15.710904378497835 46.20066024293927,15.710912159473589 46.20039950452849,15.710894434522887 46.20028929894646,15.710844620624444 46.20021889590132,15.710754497671333 46.20018268546197,15.710619472746819 46.200141098973376,15.710468078447006 46.20009395676996,15.710300020035252 46.20001863058078,15.71013294549417 46.200019670809,15.710063999207861 46.20004555880265,15.709954779718144 46.20010846583362,15.709825367334812 46.200185643990416,15.709740155452225 46.20021445995833,15.7095978600008 46.20024080222257,15.709435298637604 46.20027575661596,15.709362166147757 46.20029318215561,15.709321746574172 46.200318892298654,15.70924501465025 46.2003731121604,15.709176908852976 46.20046404855626,15.70912593824907 46.2006199379506,15.709082098558873 46.200696581456555,15.709041746284855 46.20072794679033,15.708980768498824 46.20073963902233,15.708833996835912 46.20073489545015,15.70867866945619 46.200699088541356,15.708441515485225 46.200638334448556,15.70831071551633 46.200608030417,15.708025541089574 46.20061546014786,15.707936251453559 46.200644298279066,15.707786277558592 46.20070745795111,15.707623895583048 46.200756550625464,15.707461001390076 46.20076604727856,15.707318051223131 46.20074147497647,15.707207479007135 46.20069973463324,15.707066607106702 46.20060074856272,15.70664318279083 46.20030616960038,15.705882062458974 46.19972242368199,15.70511493399327 46.19906980655475,15.704648034594763 46.1987219944351,15.70450919728616 46.19858019598824,15.704291928591257 46.19833188738757,15.704161428771785 46.198172204352296,15.703537141003846 46.19756976517491,15.703216667780048 46.197286427418,15.702992669467902 46.197180815640245,15.702837928060974 46.19713421658259,15.702717672096156 46.19710523755276,15.70259748698871 46.19708220158732,15.701539642071204 46.19672612421737,15.701342168546992 46.19668573136494,15.701050429965196 46.19663997213334,15.700655562790246 46.196565125734026,15.700286457978665 46.19649606343836,15.700088834888449 46.196443782082085,15.699934103148394 46.196397179030086,15.699769973735597 46.1962852481137,15.699587967151555 46.19611398852277,15.699336247021442 46.19584804901749,15.699059302528688 46.19561793070997,15.698759082297304 46.195383057970396,15.698344688297988 46.195118114465565,15.698137525323025 46.19498861375236,15.698007207286828 46.1948408097566,15.697833392097015 46.19463977545958,15.697738075051644 46.19455119870973,15.697513417413118 46.19439208391872,15.696752249589233 46.19379044428983,15.696406296846584 46.19351913197643,15.69616377139154 46.19330068008819,15.695964208560289 46.19309385821015,15.695790263799942 46.19288093427891,15.695446508778891 46.192414313313975)',4326

It seems like data in MySQL table is stored in WKB, but somehow when I view it it's being automatically translated to WKT!?

I'm really not sure why is that or how it happens but to extract data from MySQL table and convert to GeoJSON I used the geoPHP library with these lines of code:

// library downloaded from here https://github.com/phayes/geoPHP
include_once('../geoPHP/geoPHP.inc');

// function for calling wkb_to_json processor
function wkb_to_json($wkb) {
    $geom = geoPHP::load($wkb,'wkb');
    return $geom->out('json');
}

// checking if databasestring is UTF8 
$conn = new PDO('mysql:host=localhost;dbname=web_gis;charset=utf8','neven','gis',array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));

// using  AsBinary on geometry field
// Converts a value in internal geometry format to its WKB representation and returns the binary result
$sql = 'SELECT *, AsBinary(geometry) AS wkb FROM wkt_line';

$rs = $conn->query($sql);
if (!$rs) {
    echo 'An SQL error occured.\n';
    exit;
}

$geojson = array(
   'type'      => 'FeatureCollection',
   'features'  => array()
);

while ($row = $rs->fetch(PDO::FETCH_ASSOC)) {
    $properties = $row;

    unset($properties['wkb']);
    unset($properties['geometry']);
    $feature = array(
         'type' => 'Feature',//outer feature
         'geometry' => json_decode(wkb_to_json($row['wkb'])),
         'properties' => $properties
    );

    array_push($geojson['features'], $feature);
}

header('Content-type: application/json');
echo json_encode($geojson, JSON_NUMERIC_CHECK);
$conn = NULL;

And it worked! Enjoy...

Comments