Geotagged Articles to KML-File?

Creating and modifying plugins.
Post Reply
moenk
Regular
Posts: 52
Joined: Sat Apr 29, 2006 11:21 pm
Contact:

Geotagged Articles to KML-File?

Post by moenk » Sat Aug 02, 2008 9:46 am

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.
CMS mit S9Y: MCSE - LPIC - SQL - GIS

User avatar
garvinhicking
Core Developer
Posts: 30020
Joined: Tue Sep 16, 2003 9:45 pm
Location: Cologne, Germany
Contact:

Re: Geotagged Articles to KML-File?

Post by garvinhicking » Sat Aug 02, 2008 9:57 am

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
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/

moenk
Regular
Posts: 52
Joined: Sat Apr 29, 2006 11:21 pm
Contact:

Post by moenk » Sat Aug 02, 2008 10:17 am

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.
CMS mit S9Y: MCSE - LPIC - SQL - GIS

User avatar
garvinhicking
Core Developer
Posts: 30020
Joined: Tue Sep 16, 2003 9:45 pm
Location: Cologne, Germany
Contact:

Post by garvinhicking » Sat Aug 02, 2008 10:55 am

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
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/

moenk
Regular
Posts: 52
Joined: Sat Apr 29, 2006 11:21 pm
Contact:

Post by moenk » Sat Aug 02, 2008 11:19 am

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).
CMS mit S9Y: MCSE - LPIC - SQL - GIS

moenk
Regular
Posts: 52
Joined: Sat Apr 29, 2006 11:21 pm
Contact:

Re: Geotagged Articles to KML-File?

Post by moenk » Thu Feb 25, 2010 12:28 pm

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 ;-)
CMS mit S9Y: MCSE - LPIC - SQL - GIS

Post Reply