Skip to main content
added 6 characters in body
Source Link

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 erptable1.`locCode` != 'TEST' AND erptable1.`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.

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 erp.`locCode` != 'TEST' AND erp.`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.

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.

Removed python wrapper around SQL statement as question is only about spatial SQL; Changed table names in text to correspond to code.
Source Link
Lee Hachadoorian
  • 4.7k
  • 1
  • 18
  • 45

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

cnx = mysql.connector.connect(
        user='root', password='', unix_socket='/data/mysql/mysql.sock', database='topology')
    
fedInfo1 = pd.read_sql("""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 erp.`locCode` != 'TEST'
                    AND erp.`siteStatus` = 'ACTIVE'
                    """, cnx) #The where clauses are additional clauses (not related to the question)
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 erp.`locCode` != 'TEST' AND erp.`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.

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

cnx = mysql.connector.connect(
        user='root', password='', unix_socket='/data/mysql/mysql.sock', database='topology')
    
fedInfo1 = pd.read_sql("""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 erp.`locCode` != 'TEST'
                    AND erp.`siteStatus` = 'ACTIVE'
                    """, cnx) #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.

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 erp.`locCode` != 'TEST' AND erp.`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.

added 30 characters in body
Source Link

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

cnx = mysql.connector.connect(
        user='root', password='', unix_socket='/data/mysql/mysql.sock', database='topology')
    
fedInfo1 = pd.read_sql("""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 erp.`locCode` != 'TEST'
                    AND erp.`siteStatus` = 'ACTIVE'
                    """, cnx) #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://notepad.ltd/tmap645nhttps://filedropper.com/d/s/rqDhS1b36w5HZvCB6ON245yNlWcMhE

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

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

cnx = mysql.connector.connect(
        user='root', password='', unix_socket='/data/mysql/mysql.sock', database='topology')
    
fedInfo1 = pd.read_sql("""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 erp.`locCode` != 'TEST'
                    AND erp.`siteStatus` = 'ACTIVE'
                    """, cnx) #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://notepad.ltd/tmap645n

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

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

cnx = mysql.connector.connect(
        user='root', password='', unix_socket='/data/mysql/mysql.sock', database='topology')
    
fedInfo1 = pd.read_sql("""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 erp.`locCode` != 'TEST'
                    AND erp.`siteStatus` = 'ACTIVE'
                    """, cnx) #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.

added python syntax highlighting to code
Source Link
Matt
  • 17.9k
  • 4
  • 23
  • 59
Loading
Added a way to create a minimum re-creatable example
Source Link
Loading
edited body
Source Link
Loading
Source Link
Loading