# Android Questionpoint in polygon with spatialite

#### RB Smissaert

##### Well-Known Member
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
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
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
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