leaflet MySQL example???

[Thomas Lombard]

I have a leaflet map using GeoJSON to display many MultiPolygons

example of the GeoJSON:

var gmu001 = {

"type" : "Feature",

"properties": {

"popupContent": "This is UNIT 001",

"style": {

weight: 2,

color: "#999",

opacity: 1,

fillColor: '#FFFFFF',

fillOpacity: 0.5

}

},

"geometry": {

"type": "MultiPolygon",

"coordinates": [[[[ - 109.048595341028587, 40.834756771179649 ],..., [ - 109.048595341028587, 40.834756771179649 ] 

]]]}};

and this goes on...for a total about about 100 MultiPolygons.

I have a MySQL DB with all the unitIDs and ALL coordinates for each.

I would like to use the MySQL DB as the source for the MultiPolygons.

I would like to duplicate THIS:

http://leafletjs.com/examples/geojson-example.html

but use a MySQL DB instead of the sample-geojson.js file.

HELP PLEASE

-Tom

[Bryan McBride]

See:https://github.com/bmcbride/PHP-Database-GeoJSON/blob/master/mysql_geojson.php

[Thomas Lombard]

Two Questions:

#1 What is your 'parcels' table structure/fields/field types?

could you please send me sql that I could use to creat and populate a test tabel for the demo?

#2 What is your SELECT statment doing...you are selecting *, 

and you are doing this..AsWKB(SHAPE) AS wkb FROM parcels

and later on you     

    # Remove wkb and geometry fields from properties
    unset($properties['wkb']);
    unset($properties['SHAPE']);

This is confusing...what are you doing...maybe if I knew your table structure it would mak sense???

On Monday, February 10, 2014 7:50:39 PM UTC-7, Bryan McBride wrote:

See:https://github.com/bmcbride/PHP-Database-GeoJSON/blob/master/mysql_geojson.php

[Bryan McBride]

Thomas,

1. The 'parcels' table is just a standard MySQL spatial table with a MySQL geometry field named SHAPE. This is the standard output of OGR http://www.gdal.org/ogr/drv_mysql.html.
   
   
2. The AsWKB(SHAPE) function tells MySQL to return the geometry in Well Known Binary (WKB) format. Alternatively you could use AsText(SHAPE). We need to fetch the data in standard WKB so our geoPHP helper utility can convert it into GeoJSON. I set the $properties variable = $row so that we can unset the geometry values and remove them from the GeoJSON attributes (to keep the payload slim).

The big thing to remember is that we are using the geoPHP library to convert MySQL geometry to GeoJSON.

BRYAN

[Thomas Lombard]

Bryan:

I see what you are doing..makes sense...THANKS.

quick question for you...I'm playing around with storing the coordinates in a simple text field in my DB.

I have a field name:  'coordinates'

I have the coordinates stored in there:  like this:

[[ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0], [100.0, 1.0], [100.0, 0.0] ]]

when I json_encode($geojson);

I get this output:

"coordinates": "[[ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0], [100.0, 1.0], [100.0, 0.0] "

notice the double quotes 

it needs to be:

"coordinates": [[ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0], [100.0, 1.0], [100.0, 0.0]

How do I json_encode without those quotes for the coordinates?

JSON_NUMERIC_CHECK will not work..cause it is a tet field.

IDEAS/THOUGHTS?

[Bryan McBride]

I would highly recommend using a standard geometry format to store your coordinates. It's much more efficient, allows for spatial analysis, and can be accessed by GIS tools.

BRYAN

[Thomas Lombard]

actually THIS is the format I have:  Lat/Long

[ -102.051409782613746, 40.857924332123332 ], [ -102.051434791195206, 40.837493191725123 ], [ -102.051497382833745, 40.822809276988195 ]

and wan this loaded with out the commas before and after

On Wednesday, February 12, 2014 8:13:48 AM UTC-7, Thomas Lombard wrote:

I will get to using standard geometry format, BUT for now I have all my coordinates in this format:

 [100.0, 0.0], [101.0, 0.0], [101.0, 1.0], [100.0, 1.0], [100.0, 0.0]

and simply want to store those coordinates in a text field...and pull that field through the json_encode process to creata  valid GeoJSON format to display the Polygon onto a Leaflet map.

How do I remove the quotes prior/after???

"coordinates": "[[ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0], [100.0, 1.0], [100.0, 0.0] "

[Gaute Langaas]

Hi,

Just replace "[ with [ and replace ]" with ].  Simple as that.

Good luck,

Br,

Gaute Langaas

--
 
---
You received this message because you are subscribed to the Google Groups "Leaflet" group.
To unsubscribe from this group and stop receiving emails from it, send an email to leaflet-js+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Thomas Lombard]

I wish it was that easy :>)

the quotes are added by the echo json_encode

while ($row = mysql_fetch_assoc($rs)) {

    $properties = $row;

    $geometry = $row;

    # Remove fields

    unset($properties['coordinates']);

    unset($properties['type']);

    unset($geometry['unitID']);

    unset($geometry['milesSq']);

    unset($geometry['acres']);

    $feature = array(

        'type' => 'Feature',

        'properties' => $properties,

        'geometry' => array($geometry)        

    );

    # Add feature arrays to feature collection array

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

}

echo json_encode($geojson);

results:  

{"type":"FeatureCollection","features":[{"type":"Feature","properties":{"unitID":"001","milesSq":"111.000000","acres":"222.000000"},"geometry":[{"type":"Polygon","coordinates":"[[[-109.048595341028587, 40.834756771179649], [-108.966734749488324, 40.562222787215255],  [-109.048595341028587, 40.834756771179649]]]"}]}]}

--
 
---
You received this message because you are subscribed to a topic in the Google Groups "Leaflet" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/leaflet-js/CuAMElIyDO0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to leaflet-js+...@googlegroups.com.

[prince shahnawaz]

I am having the same problem.. Did you find any solution?