Android Question point in polygon with spatialite

RB Smissaert

Well-Known Member
Licensed User
Running this SQL in the spatialite gui application:

SELECT ID FROM P_POINTS WHERE Within(GEOMETRY, polygonfromtext('POLYGON(-2.165476 52.604321, -2.167375 52.606777, -2.16444 52.606914, -2.163287 52.604595, -2.165476 52.604321)', 4326)) = 1

Geometry is the column in P_POINTS holding lat/lon points as geometry blobs. This column is fine as tested with the AsText and x and y functions.

This SQL should give me results but I get nil. No error though.

Any idea what could be the problem here?


RBS
 

RB Smissaert

Well-Known Member
Licensed User
Running this SQL in the spatialite gui application:

SELECT ID FROM P_POINTS WHERE Within(GEOMETRY, polygonfromtext('POLYGON(-2.165476 52.604321, -2.167375 52.606777, -2.16444 52.606914, -2.163287 52.604595, -2.165476 52.604321)', 4326)) = 1

Geometry is the column in P_POINTS holding lat/lon points as geometry blobs. This column is fine as tested with the AsText and x and y functions.

This SQL should give me results but I get nil. No error though.

Any idea what could be the problem here?


RBS
If I simplify it:

SELECT Within(Makepoint(-2.5, 51.5, 4326), polygonfromtext('POLYGON(-2 52, -2.5 51, -3 52, -2 52)', 4326))

I get as result - 1 and I understand that means unknown.
So, why doesn't this give me the result 1?
Spatialite gui doesn't recognize ST_Within as a function and not sure what the difference is between Within and ST_Within.

RBS
 

RB Smissaert

Well-Known Member
Licensed User
If I simplify it:

SELECT Within(Makepoint(-2.5, 51.5, 4326), polygonfromtext('POLYGON(-2 52, -2.5 51, -3 52, -2 52)', 4326))

I get as result - 1 and I understand that means unknown.
So, why doesn't this give me the result 1?
Spatialite gui doesn't recognize ST_Within as a function and not sure what the difference is between Within and ST_Within.

RBS
The problem lies with making the polygon blob as this Sub shows:

B4X:
Public Sub TestPolygon
 Dim strSQL As String
 Dim SPL_Stmt As Spatialite_Stmt
 strSQL = "select 'Point: ' || hex(MakePoint(-2, 52, 4326)) || " & _
      "', Polygon: ' || hex(polygonfromtext('POLYGON(-2 52, -2.5 53, -3 52, -2 52)', 4326))"
 SPL_Stmt = SPL_DB.Prepare(strSQL)
 SPL_Stmt.Step
 Log("Hex Point and Polygon: " & SPL_Stmt.ColumnString(0))
 SPL_Stmt.Close
End Sub
I get this result:

Hex Point and Polygon: Point: 0001E610000000000000000000C00000000000004A4000000000000000C00000000000004A407C0100000000000000000000C00000000000004A40FE, Polygon:

I take it polygonfromtext produces Null, but MakePoint works OK.
So, how should I produce this polygon blob?

RBS
 

RB Smissaert

Well-Known Member
Licensed User
The problem lies with making the polygon blob as this Sub shows:

B4X:
Public Sub TestPolygon
 Dim strSQL As String
 Dim SPL_Stmt As Spatialite_Stmt
 strSQL = "select 'Point: ' || hex(MakePoint(-2, 52, 4326)) || " & _
      "', Polygon: ' || hex(polygonfromtext('POLYGON(-2 52, -2.5 53, -3 52, -2 52)', 4326))"
 SPL_Stmt = SPL_DB.Prepare(strSQL)
 SPL_Stmt.Step
 Log("Hex Point and Polygon: " & SPL_Stmt.ColumnString(0))
 SPL_Stmt.Close
End Sub
I get this result:

Hex Point and Polygon: Point: 0001E610000000000000000000C00000000000004A4000000000000000C00000000000004A407C0100000000000000000000C00000000000004A40FE, Polygon:

I take it polygonfromtext produces Null, but MakePoint works OK.
So, how should I produce this polygon blob?

RBS
This works fine though:

SELECT hex(polygonfromtext('POLYGON((-2 52,-2.5 53,-3 52,-2 52))', 4326))

So problem seems to have been missing brackets and I think all fixed now.

RBS
 
Top