2

I have two tables (Table1, 10k rows, and Table2, 100k rows). Table1 contains latitude and longitude columns while Table2 contains a geometry column (representing a polygon). I wanted to merge these two tables based on whether the latitude and longitude in Table1 fall within the Polygon of Table2. To do this, I came up with the following code:

SELECT table1.`locCode`, table2.`FEDUID`, table2.`FEDENAME`
FROM `Table2` table2
  RIGHT JOIN `Table1` table1 
  ON ST_WITHIN(POINT(table1.`longitude`, table1.`latitude`), table2.`geom`)
WHERE table1.`locCode` != 'TEST' AND table1.`siteStatus` = 'ACTIVE'

The where clauses are additional clauses (not related to the question).

The issue is that this code seems to work for only SOME rows (i.e. I don't have the amount of matches that I would like to see).

For example, I plotted the lat and long of one of the blank matches: Point

I verified it and this point should fall within the Polygon (in wkt format) of the geom column in Table2:

Polygon Representation

The problem after running the SQL query, I don't get a match between these two. Any ideas what I maybe missing?


To recreate the problem, I have configured the following SQL code and since I do need to give geometries as wkt formats, I am unable to include it as part of the problem, however, I have shared here: https://filedropper.com/d/s/rqDhS1b36w5HZvCB6ON245yNlWcMhE

This will contain SQL statements necessary to re-create part of table1 and table2. I hope this helps.

6
  • You will need to have geometry in both tables to be able to use ST_WITHIN mariadb.com/kb/en/st-within ST_WITHIN(g1,g2) >Returns 1 or 0 to indicate whether geometry g1 is spatially within geometry g2.
    – Mapperz
    Apr 6, 2022 at 1:08
  • @Mapperz that's correct but I have two type of geometries in my ST_WITHIN function: (i) POINT(table1.`longitude`, table1.`latitude`) converts it to a geom type object, and (ii) the Polygon object (stored in my table2)
    – Learner120
    Apr 6, 2022 at 1:11
  • 1
    On the border is not within
    – Ian Turton
    Apr 6, 2022 at 9:02
  • @IanTurton it actually isn't on the border, it's slightly inside the border when I do try to zoom in, I will update the question to have both the lat and long and also the Polygon
    – Learner120
    Apr 6, 2022 at 13:45
  • What projection/coordinate system is your geom column in? Make sure that you are transforming your point geometry to the same coordinate system. Apr 6, 2022 at 15:24

1 Answer 1

4

This is not a definitive answer, but should help you narrow it down.

You say in a comment that the point "isn't on the border [of the polygon], it's slightly inside the border when I do try to zoom in". But it is very suspicious that the point you expect to be within the polygon is so close to the border. This could be a problem of the spatial relationship being compared in latitude and longitude coordinates. The polygon is made of a series of vertex coordinates. The arcs between these vertices will be constructed as a straight line between the coordinates, but if the coordinates are in a spherical coordinate system, the linestring connecting them will be straight in a fictional, planar version of those spherical coordinates. Relationship comparisons at the edges of the linestring may not be accurate.

In order to test this, you should select points within a short distance of the polygon, and check to see whether those points fall outside, on the boundary, or inside the polygon. You can then visually compare to see whether there are points that the MariaDB spatial library thinks are outside, but look upon visual inspection to be inside.

If this turns out to be the case you will want to transform the coordinates to an appropriate local coordinate system before doing the spatial comparison.

You can check the relationships as follows:

SELECT table1.<name or id column>, table2.<name or id column>,
  ST_Disjoint(table1.`geom`, table2.`geom`), 
  ST_Touches(table1.`geom`, table2.`geom`), 
  ST_Within(table1.`geom`, table2.`geom`)
FROM `Table2` table2 JOIN (
    SELECT t.<name or id column>, POINT(t.`longitude`, t.`latitude`) AS `geom`
    FROM `Table1` t
  ) table1
  ON ST_WITHIN(table1.`geom`, ST_Buffer(table2.`geom`, 0.01));

This query uses a buffer radius of 0.01 to find points near the comparison polygon. Since your coordinates are in decimal degrees, this should yield a distance of about 1km near the equator. This will be smaller near Toronto (shown in your map image), but should still be big enough to identify the error without requiring spatial comparisons between too many non-intersecting geometries.

The comparisons are as follows:

  • ST_Disjoint will return 1 for any points that are near but not intersecting a nearby polygon.
  • ST_Touches will return 1 for any points that are on the boundary of a polygon.
  • ST_Within will return 1 for any points that are in but not on the border of a polygon. Note that the MariaDB documentation is not clear about whether points on the border count as within, but assuming it follows PostGIS (which itself follows the SQL-MM 3: 5.1.30 specification), it should behave the same way. You can easily test this with simple geometries yourself.

After running this query you should look for points that you expect to be within the polygon from your visual inspection, but that this query shows as either disjoint (non-intersecting) or touching (on the border).

6
  • this is great and is actually super insightful for someone, like me, who doesn't know a lot about spatial geometries. I think the issue is that I am creating a Point but not giving it an SRID (i.e. the way I create the point is by using the following: POINT(table1.longitude, table1.latitude) with no SRID given). I will take a look at the query you provided and try to see what the issue is.
    – Learner120
    Apr 7, 2022 at 6:53
  • 1
    That sounds very plausible. You could try using geography columns instead of geometry, or choose an appropriate projection and reproject the polygons. I always like to be explicit with things like SRIDs. Even if it's just a one-off query, you might want to dig it out another time and you won't have to second guess yourself. Apr 7, 2022 at 7:47
  • 1
    MariaDB appears to ignore SRIDs and just do ST_Within using planar coordinates. On the one hand, if your data are in different coordinate systems, this could be the problem. On the other hand, not assigning an SRID to the point won't create a problem as long as the coordinates you pass in are in the same SRID as the polygon. What is the value of ST_SRID(Table2.geom)? Apr 8, 2022 at 3:05
  • @LeeHachadoorian the Polygon has an SRID of 4327 but I'm not sure how to give an SRID to a point that is constructed with a function (i.e. POINT (table1.longitude, table1.latitude)
    – Learner120
    Apr 11, 2022 at 14:40
  • @Learner120 MariaDB ignores SRIDs. I have tested this putting in incompatible SRIDs. The query engine ignores them and treats the coordinates as being in the same coordinate reference system regardless. mariadb.com/kb/en/st_srid "In MariaDB, the SRID value is just an integer associated with the geometry value. All calculations are done assuming Euclidean (planar) geometry." Apr 11, 2022 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.