Page 1 of 1
Geotagged Articles to KML-File?
Posted: Sat Aug 02, 2008 9:46 am
by 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.
Re: Geotagged Articles to KML-File?
Posted: Sat Aug 02, 2008 9:57 am
by 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
Posted: Sat Aug 02, 2008 10:17 am
by 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.
Posted: Sat Aug 02, 2008 10:55 am
by garvinhicking
Hi!
moenk wrote: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
Posted: Sat Aug 02, 2008 11:19 am
by 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).
Re: Geotagged Articles to KML-File?
Posted: Thu Feb 25, 2010 12:28 pm
by 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