0

I'm pretty new to the GIS field, so might be ignoring something obvious here.

I'm trying to match Mlab speed test data (each record had lat long attribute) with map of administrative boundaries of Jamaica (uploaded as csv, last column is wkt).

SELECT 
    date,
    ROUND(avg(a.MeanThroughputMbps),3) as Mbps, 
    ROUND(APPROX_QUANTILES(a.MeanThroughputMbps, 100) [SAFE_ORDINAL(50)],3) AS download_MED,
    ROUND(max(a.MeanThroughputMbps),3) as maxMbps,
    ROUND(min(a.MeanThroughputMbps),3) as minMbps,
    count(a.MeanThroughputMbps) as number_speed_tests,
    ROUND(avg(a.MinRTT),3) as latency,
    count(client.Network.ASNumber) as number_ISP,
    string_field_3
FROM `measurement-lab.ndt.unified_downloads` as unified_downloads
JOIN `atlas-323422.Jamaica_boundaries.JAM_boundaries` as geo
ON ST_Within(ST_GeogPoint(client.Geo.Longitude, client.Geo.Latitude),(ST_GEOGFROMTEXT(geo.string_field_6, make_valid => TRUE)))
WHERE date >= "2021-06-01" and client.Geo.CountryCode = "JAM" 
group by string_field_3, date

Getting the following error:

ST_GeogFromText failed: Unexpected 'geometry' at position 0.

I did similar operations with other countries, same source for shapefiles, same query.

Jamaica table: enter image description here

2
  • 1
    look at your data content, search for one whose string_field_6 starts with the word geometry (maybe the csv header got inserted as a record?) or is null
    – JGH
    Jan 25 at 13:28
  • Yup, that was the one, thanks:)
    – LuranLuran
    Jan 25 at 17:53

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.