Geotagged Articles to KML-File?

[moenk]

Does somebody already have coded a PHP-file that could be used with modrewrite an produces a KML-output with all upcoming events/articles? Anything similar might be helpful also.
If not - which SQL would be fine to give me a table with name, date, lon and lat? The problem is that lon and lat are coded as seperate records in the entryproperties table with the article number as foreign key.

[garvinhicking]

Hi!

That sounds interesting. The geotag plugin should be easily changable to allow this, and could offer the KML as something like http://blog/plugin/geo.kml

The SQL would be something like:

Code: Select all

SELECT e.title, ep1.value AS geo_long, ep2.value AS geo_lat
JOIN serendipity_entryproperties AS ep1
ON (ep1.entryid = e.id AND ep1.property = 'ep_geo_long')
JOIN serendipity_entryproperties AS ep2
ON (ep2.entryid = e.id AND ep2.property = 'ep_geo_lat')

This should return rows containing the entry title, value and latitude...

Regards,
Garvin

[moenk]

Thanks for your quick support! The SQL doesn't work, I just tried. I also wondered why the entries table is not in a FROM clause.
Anyway, would it be better to wait and the KML-export is done by the Geotag-Plugin? I just wanted to start to make a little PHP-file to gather the data from the tables and rewrite the URL of the "events.kml" to that one.
I did a similar hack http://www.geocaching-events.de/upcoming.php for another purpose and it works well.

[garvinhicking]

Hi!

Thanks for your quick support! The SQL doesn't work, I just tried.

The error message always helps.

Try this:

Code: Select all

SELECT e.title, ep1.value AS geo_long, ep2.value AS geo_lat
FROM serendipity_entries
JOIN serendipity_entryproperties AS ep1
ON (ep1.entryid = e.id AND ep1.property = 'ep_geo_long')
JOIN serendipity_entryproperties AS ep2
ON (ep2.entryid = e.id AND ep2.property = 'ep_geo_lat')

You were right, the FROM was missing.

Anyway, would it be better to wait and the KML-export is done by the Geotag-Plugin? I just wanted to start to make a little PHP-file to gather the data from the tables and rewrite the URL of the "events.kml" to that one.
I did a similar hack http://www.geocaching-events.de/upcoming.php for another purpose and it works well.

I will try to work on the plugin to create KML support for it, but it might take until the end of next week.

Regards,
Garvin

[moenk]

This would be great - thank you! No need to hurry.
Very nice would be if the KML would be included in the sitemap and the icon could be selected from the media gallery (or just a path specified), or the kategorie icons could by used (prohably the best way).

[moenk]

At this time, the old thing gets to become reality.
Minor change: Data will be exported to CSV for being displayed on OpenLayers, not KML.
This is the query I used:

Code: Select all

SELECT e.id, e.title, e.timestamp, e.body, ec.categoryid, ep1.value AS geo_long, ep2.value AS geo_lat
FROM berlin_entries AS e
JOIN berlin_entryproperties AS ep1 ON ( ep1.entryid = e.id
AND ep1.property = 'geo_long' )
JOIN berlin_entryproperties AS ep2 ON ( ep2.entryid = e.id
AND ep2.property = 'geo_lat' )
JOIN berlin_entrycat AS ec ON ( ec.entryid = e.id )
WHERE ec.categoryid =5

Just as a feedback