Location suggestions from lat/lngs

[nimmolo]

If we want to offer location suggestions that encompass a given lat/lng, this may be a good time to migrate to a “geometry” column for our Location table. This would allow us to query directly for locations containing a lat/lng, or to get results sorted by area ascending.

However, we should think clearly about the cost of this. It would mean rewriting the methods that parse and update a location’s north/south/east/west bounds. Once we go down this path, we will need to be parsing and updating the polygons in a single column, using MySQL’s geometry parsing functions.

It might be as simple as converting north, south, east and west into get/set instance methods that either return these parsed values or update the polygon. But it might not be that easy. I want to hear some advise, or maybe check out how iNat does it.

---

For more on the story, I asked Github’s AI:

We have a database table "Locations" consisting of arbitrary named locations defined by minimum bounding rectangles, without polygonal boundaries. In most cases these correspond to real places in Google Maps. Is it advisable to convert the north, south, east and west columns of this table to a MySQL "geometry" column, containing the MBR? Could we then populate the locations with more precise polygonal boundaries later?

The language aggregator had this to say:

Converting the north, south, east, and west columns of your "Locations" table to a MySQL "geometry" column that contains the Minimum Bounding Rectangle (MBR) can be advantageous for several reasons:

• Spatial Queries: MySQL supports spatial data types and indexes, which allow for efficient querying of spatial data, such as finding all locations within a certain distance from a point, or finding all locations that intersect with a given area. This can significantly improve performance for geospatial queries.
• Data Integrity: Using a "geometry" column ensures that the data conforms to the geometry standards, helping maintain the integrity of your spatial data.
• Flexibility: Storing your location data as geometry objects provides the flexibility to store more complex shapes in the future, such as precise polygonal boundaries. MySQL's "geometry" data type supports various shapes, including points, lines, polygons, and multipolygons.
• Standardization: Using geometry columns aligns with the Open Geospatial Consortium (OGC) standards, making it easier to integrate with other geospatial databases, tools, and applications.

Regarding the second part of your question, yes, you can later populate the locations with more precise polygonal boundaries. Initially, you can store the MBR as a polygon geometry, and when more precise boundaries are available, you can update the geometry data for those locations with detailed polygons. This approach provides a smooth transition from simpler bounding rectangles to more complex shapes without needing to alter the database schema.

Here's a simplified example of how you might convert your existing columns to a geometry column and later update it with a more precise polygon:

• Converting to Geometry Column:

ALTER TABLE Locations 
ADD COLUMN geom GEOMETRY;
UPDATE Locations 
  SET geom = ST_GeomFromText(
    CONCAT('POLYGON((', west, ' ', south, ',', east, ' ', south, ',', east, ' ', north, ',', west, ' ', north, ',', west, ' ', south, '))’)
);

• Updating with More Precise Polygon:

UPDATE Locations
SET geom = ST_GeomFromText(
'POLYGON((lng1 lat1, lng2 lat2, ..., lngN latN, lng1 lat1))’)
WHERE id = ?;

Remember to create a spatial index on your geometry column to improve the performance of spatial queries:

ALTER TABLE Locations ADD SPATIAL INDEX(geom);

This approach provides both immediate benefits for spatial querying and the flexibility to enhance the geographic detail of your locations in the future.

[nimmolo]

I then asked (or rather, i thought of this question, and then noticed it had suggested the same question, and I merely had to click on the question):

How can I retrieve the coordinates of the Minimum Bounding Rectangle (MBR) from a MySQL geometry column?

It responded:

To retrieve the coordinates of a Minimum Bounding Rectangle (MBR) from a MySQL geometry column, you can use the ST_Envelope function to get the MBR of the geometry, followed by ST_AsText or ST_AsWKB to convert the geometry into a human-readable format or a binary format, respectively. However, if you already have the MBR stored as a geometry object in your column and simply want to extract its coordinates, you can directly use ST_AsText (or ST_AsWKB for binary).

Here's how you can retrieve the coordinates of an MBR stored in a geometry column named geom_column in a table called YourTable:

SELECT ST_AsText(geom_column) AS MBR_Text
FROM YourTable;

This query returns the MBR as a text string in the WKT (Well-Known Text) format, for example, POLYGON((SW_Lng SW_Lat, SE_Lng SE_Lat, NE_Lng NE_Lat, NW_Lng NW_Lat, SW_Lng SW_Lat)).

If you need to extract individual coordinates (e.g., for processing in an application), you can use specific functions like ST_X and ST_Y in combination with ST_PointN and ST_ExteriorRing to extract points from the MBR polygon. However, since an MBR is a rectangle, you might only need the southwest and northeast corners to define it fully. Here's an example that extracts these corners:

SELECT 
    ST_X(ST_PointN(ST_ExteriorRing(geom_column), 1)) AS SW_Lng,
    ST_Y(ST_PointN(ST_ExteriorRing(geom_column), 1)) AS SW_Lat,
    ST_X(ST_PointN(ST_ExteriorRing(geom_column), 3)) AS NE_Lng,
    ST_Y(ST_PointN(ST_ExteriorRing(geom_column), 3)) AS NE_Lat
FROM YourTable;

This query assumes that the first point in the exterior ring of the polygon is the southwest corner and the third point is the northeast corner, which is typical for an MBR defined as a POLYGON in WKT format. Adjust the point indices as necessary based on how your MBRs are structured.

---

I wanted to read an example of how we'd do that in Ruby, and it gave me these examples.

ActiveRecord, by itself, does not provide a direct method to extract individual coordinates of a Minimum Bounding Rectangle (MBR) from a geometry column.

However, you can execute raw SQL queries within ActiveRecord to utilize spatial functions provided by your database system (like MySQL) to retrieve the MBR coordinates. Here's how you can do it in ActiveRecord:

YourModel.connection.select_value("SELECT ST_AsText(ST_Envelope(geom_column)) FROM your_table WHERE id = 1")

To extract individual coordinates from the MBR, you would typically need to parse the returned WKT (Well-Known Text) string in Ruby, as the direct extraction of these coordinates into separate variables is not a built-in feature of ActiveRecord or basic SQL functions.

Assuming you have a WKT string for a POLYGON, you can parse it like so:

wkt_string = "POLYGON((SW_Lng SW_Lat, SE_Lng SE_Lat, NE_Lng NE_Lat, NW_Lng NW_Lat, SW_Lng SW_Lat))"
coordinates = wkt_string.scan(/POLYGON\(\(\s*(.+?)\s*\)\)/).first.first.split(',').map { |pair| pair.split.map(&:to_f) }
# This will give you an array of coordinate pairs [[SW_Lng, SW_Lat], [SE_Lng, SE_Lat], ...]

[JoeCohen]

This could be very useful for iNat imports.

Each iNat Observation has a:

• location, which combines lat/lng (ex: :location=>"31.8813,-109.244",), and
• public_positional_accuracy (in meters) (ex: :public_positional_accuracy=>20).
  The latter can be large. (Ex. https://inaturalist.org/observations/220370929, with "public_positional_accuracy":14305.

So any suggested MO Location should be encompass the iNat location +/- public_positional_accuracy.
It would be great to get suggestions from the db.

[nimmolo]

Coming very soon.

[nimmolo]

Now I understand what you're suggesting, better. Thanks for explaining at the meeting.

[nimmolo]

Pasting this note here for storage, because I removed it from the "phase 2a" PR.

Desired behavior is more complex.

• either suggest only MO Locations matching your lat/lngs, or
• if we don't have a matching Location: ask Google. Either:
  • autofill it with a brand new reverse-geolocated location, or
  • offer a button to suggest and create such a location.

The way the last two could work is send the lat/lng to Google via JS. Using whatever Google returns for "smallest encompassing" places (preferring a colloquial_area, locality or sublocality) and using their bounding boxes, we would offer the user the choices, and whichever one they pick, automatically create the locality in the DB in the background, and attach it to the obs.

Note: this will cost us something for lookups. It may also go unused, since people can still override suggested Locations and create their own.

Now's a good time to clarify what we want to happen, so we're not doing more than we need to.