Generating GeoJSON from the database

Support - Assistance technique
1

Hi,

Please find attached some PHP code I used to connect to a Mysql
version of the DHIS2 db and produce GeoJSON.

Would be great if we could have a service in DHIS 2 produce the same.
Especially because this would allow us to easily create maps (map
views) for just one district or province, and not the whole country,
as many local users would want (though we may need some more machinery
for the display of overlays in that case). This means that. a map view
should be attached to an orgunit.

Knut

geojson.php (1.72 KB)

2

Working on converting all our data to geojson, I've changed my mind
for representation in the database somewhat. Instead of just having
the coordinates in one field and the type of geometry in another, it
will be both more efficient and easier to have just store the full
geometry string in one field, like this:

{"type": "Point", "coordinates": [6.18218, 45.5949]}

Similarly for Polygon and Multipolygon.

The problem we are encountering is that even with datatype text (in
Postgresql), the multipolygon strings sometimes are too long for the
field - not sure why that happens since text is defined as unlimited?
Our solution has been to both generalize (simplify) and limit
coordinates to two decimals, but it is still somewhat problematic
(these gemetries come from the PostGIS geometry format which works
well)

You can see how the above JSON snippet fits into the fully generated
GeoJSON file below:

{"type": "FeatureCollection", "features": [{"geometry":
{"type": "Point", "coordinates": [6.18218, 45.5949]}, "type":
"Feature", "properties": {"name": "Col d'Arclusaz"}, "id": 472},
{"geometry": {"type": "Point", "coordinates": [6.27827, 45.6769]},
"type": "Feature", "properties": {"name": "Pointe de C\u00f4te
Favre"}, "id": 458}

···

On Thu, May 20, 2010 at 9:13 AM, Knut Staring <knutst@gmail.com> wrote:

Hi,

Please find attached some PHP code I used to connect to a Mysql
version of the DHIS2 db and produce GeoJSON.

Would be great if we could have a service in DHIS 2 produce the same.
Especially because this would allow us to easily create maps (map
views) for just one district or province, and not the whole country,
as many local users would want (though we may need some more machinery
for the display of overlays in that case). This means that. a map view
should be attached to an orgunit.

Knut

--
Cheers,
Knut Staring

3

Much simplified PHP that works with the db representation below.

geojson.php (814 Bytes)

···

On Thu, May 20, 2010 at 1:26 PM, Knut Staring <knutst@gmail.com> wrote:

Working on converting all our data to geojson, I've changed my mind
for representation in the database somewhat. Instead of just having
the coordinates in one field and the type of geometry in another, it
will be both more efficient and easier to have just store the full
geometry string in one field, like this:

{"type": "Point", "coordinates": [6.18218, 45.5949]}

Similarly for Polygon and Multipolygon.

The problem we are encountering is that even with datatype text (in
Postgresql), the multipolygon strings sometimes are too long for the
field - not sure why that happens since text is defined as unlimited?
Our solution has been to both generalize (simplify) and limit
coordinates to two decimals, but it is still somewhat problematic
(these gemetries come from the PostGIS geometry format which works
well)

You can see how the above JSON snippet fits into the fully generated
GeoJSON file below:

{"type": "FeatureCollection", "features": [{"geometry":
{"type": "Point", "coordinates": [6.18218, 45.5949]}, "type":
"Feature", "properties": {"name": "Col d'Arclusaz"}, "id": 472},
{"geometry": {"type": "Point", "coordinates": [6.27827, 45.6769]},
"type": "Feature", "properties": {"name": "Pointe de C\u00f4te
Favre"}, "id": 458}

On Thu, May 20, 2010 at 9:13 AM, Knut Staring <knutst@gmail.com> wrote:

Hi,

Please find attached some PHP code I used to connect to a Mysql
version of the DHIS2 db and produce GeoJSON.

Would be great if we could have a service in DHIS 2 produce the same.
Especially because this would allow us to easily create maps (map
views) for just one district or province, and not the whole country,
as many local users would want (though we may need some more machinery
for the display of overlays in that case). This means that. a map view
should be attached to an orgunit.

Knut

--
Cheers,
Knut Staring

--
Cheers,
Knut Staring

4

On the topic of representation and transmission of geometries, I
notice that http://waterandhealth.eu/ does not use GeoJSON, but rather
the Well Known Text (WKT) format, which seems slightly more compact
(but is perhaps harder to process on the client?). Below is a snippet
of their output, which can be compared to mine
(http://109.74.202.200/ke/geojson.php\):

{
    "geometries": [{
        "fid": "1",
        "geom": "POLYGON((-2557240.9748832
6329005.6983322,-2564581.4426868 6353888.5764035,-2553392.6270149
6391595.0284018,-2502799.807139 6423373.1582081,-2473735.0208929
6401109.7954626,-2456127.2424861 6347915.0302067,-2467612.2173775
6310880.8052258,-2469614.0814956 6310851.8313805,-2504979.3381634
6246832.1685795,-2487429.17942 6188194.8654726,-2497166.6999307
6155148.5968215,-2550830.1263206 6119993.6983044,-2584648.0144422
6122612.2128699,-2585395.8731606 6100662.2483157,-2609950.1694991
6090001.439647,-2624827.4209589 6102934.6409606,-2619099.8045086
6146070.9573777,-2645062.206619 6208261.4281141,-2633371.4023808
6193731.1831049,-2631973.584123 6230944.3385753,-2601190.04894
6250081.9372284,-2557240.9748832 6329005.6983322))",
        "country_id": "4",
        "country_name": "Albania"
    },
    {
        "fid": "2",
        "geom": "POLYGON((-3787328.9817063
7292282.8224448,-3768426.9845767 7270348.9602439,-3776802.3350657
7268439.5634358,-3799035.4668783 7278575.4189864,-3787328.9817063
7292282.8224448))",
        "country_id": "133",
        "country_name": "Andorra"
    }]
}

···

On Thu, May 20, 2010 at 1:26 PM, Knut Staring <knutst@gmail.com> wrote:

Working on converting all our data to geojson, I've changed my mind
for representation in the database somewhat. Instead of just having
the coordinates in one field and the type of geometry in another, it
will be both more efficient and easier to have just store the full
geometry string in one field, like this:

{"type": "Point", "coordinates": [6.18218, 45.5949]}

Similarly for Polygon and Multipolygon.