From the docs:
ST_TOUCHES Returns TRUE if the only points in common between g1 and g2 lie in the union of the boundaries of g1 and g2.
In other words, you're seeing if your buffered points share an edge with the linestring. You want ST_DWITHIN. I don't have your data but try:
SELECT COUNT(*)/ST_LENGTH(routea.geom)::float as Count FROM flickr_edin, routea
You can use Max on geom_centroid as well. See how this:
Max(p_density) as max_density,
Max(geom_centroid) as geom_centroid
SELECT COUNT(a.geom) as p_density,ST_ASTEXT(ST_CENTROID(b.geom)) as geom_centroid
FROM flickr_edin a JOIN grid100m b ON ST_WITHIN(a.geom, b.geom)
GROUP BY b.geom
Compares to this:
Max(p_density) as ...
An inline SQL test to demonstrate what goes wrong and how to fix the problem.
ST_GeomFromText('POINT (326253.33 674110.63)'),
ST_Buffer(ST_GeomFromText('POINT (326253.33 674110.63)',27700),10));
ERROR: contains: Operation on mixed SRID geometries
(Polygon, 27700) != (Point, 0)
SQL state: XX000
You need to specify the SRID of the table you create. To create it with the same SRID as the flickr_edin table:
CREATE TABLE temppat (id serial, geom geometry(Geometry,27700));
INSERT INTO temppat (geom) VALUES (ST_GeomFromText('POINT (326253.33 674110.63)'));
To find out the SRID(s) of a table:
SELECT DISTINCT ON (ST_SRID(geom)) ST_SRID(geom) FROM ...
To export a valid 'line'-geojson you could set the verbosity to 'geom'. If you are facing timeout issues you could set a larger timeout value than the default 25.
api = overpass.API(timeout=500)
# api.get already returns a FeatureCollection, a GeoJSON type
res = api.get("""
For #2 - Use coded value domain descriptions instead of codes:
I think it's possible to create a dynamic join to a table in ArcGIS Online:
Create results as hosted feature layer view allows the data to stay up
to date as the source data changes. Hosted feature layer views
containing joins will be read-only and do not consume credits for
Use Overpass Turbo web interface, zoom to your region of interest, click Wizard and enter this string: sport:skate or sport:extreme.
Using double points : instead of equal symbol =, it uses wildcards (thus finding variants like skate or skateboard). The wizard automatically converts the string to a valid overpass query when you click create and run query.