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.
Geotagged Articles to KML-File?
-
- Core Developer
- Posts: 30022
- Joined: Tue Sep 16, 2003 9:45 pm
- Location: Cologne, Germany
- Contact:
Re: Geotagged Articles to KML-File?
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:
This should return rows containing the entry title, value and latitude...
Regards,
Garvin
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')
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/
# 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/
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.
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.
-
- Core Developer
- Posts: 30022
- Joined: Tue Sep 16, 2003 9:45 pm
- Location: Cologne, Germany
- Contact:
Hi!
Try this:
You were right, the FROM was missing.
Regards,
Garvin
The error message always helps.moenk wrote:Thanks for your quick support! The SQL doesn't work, I just tried.
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')
I will try to work on the plugin to create KML support for it, but it might take until the end of next week.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.
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/
# 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/
Re: Geotagged Articles to KML-File?
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:
Just as a feedback
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