Android Question Spatialite

[Terradrones]

Hi All

I have read through Warwound's article, but I have no flippen idea how to use Spatialite to convert Lats\Longs to different Coordinate systems. Must be oldage or stupid...maybe both!

Any help or push in the right direction please?

 

[Terradrones]

Hi All

I need a push again please.

Gvoulg has helped me a lot, but I am doing something wrong.

In the "Addcoords" Activity I have a routine to plot the coordinates on Google Map:

[/
Sub PlotPoints(A As Int)
    Dim AveY, AveX As Double
    Dim cp As CameraPosition
    Dim Co As CircleOptions
 
    File.MakeDir(File.DirRootExternal & "/CEASER/","Data/Projections")
 
    Co.Initialize
    'Dim BitMap1 As Bitmap  = LoadBitmapResize(File.DirRootExternal & "/CEASER/DATA/", "Point.png",8dip,8dip,True)
    Try
        gmap.Clear
 
        If CGlobals.CoordCode=1 Then
            'Site Coords
            Dim rs As ResultSet = CGlobals.sql1.ExecQuery("SELECT Name, East, North, Elevation, Description FROM SCoords")
        else if CGlobals.CoordCode=2 Then
            'Global Coords
            Dim rs As ResultSet = CGlobals.sql1.ExecQuery("SELECT Name, East, North, Elevation, Description FROM GCoords")
        Else
            'Job Coords
            Dim rs As ResultSet = CGlobals.sql1.ExecQuery("SELECT Name, East, North, Elevation, Description FROM Coords")
        End If
        i=0: AveY=0: AveX=0
     
        Do While rs.NextRow
            Dim row(5) As Object
            row(0) = rs.GetString("Name")
            row(1) = NumberFormat2(rs.GetDouble("East"),1,3,3,False)
            row(2) = NumberFormat2(rs.GetDouble("North"),1,3,3,False)
            'row(3) = NumberFormat2(rs.GetDouble("Elevation"),1,3,3,False)
            Geo.get_point_from_local(row(1),row(2))
            Co.StrokeWidth(2)
'            co.Center2(Engine.Lat,Engine.Lon).Radius(Radius.SelectedItem).FillColor(Colors.White).StrokeColor(Colors.DarkGray)
'            gmapextra.AddCircle(gmap,co)
            'gmap.AddMarker(Engine.Lat,Engine.Lon, "New Marker")
            'gmap.AddMarker3(Engine.lat,Engine.Lon,row(0),Bitmap1)
            i=i+1
            AveY=AveY+row(1)
            AveX=AveX+row(2)
        Loop
        rs.Close
        If (AveY<>0 Or AveX<>0) And A=0 Then
            'Zoom in to center of points
            AveY=AveY/i
            AveX=AveX/i
            Convert(row(1),row(2),34)
'            Engine.Hart94_YX(AveY,AveX,19)
'            cp.Initialize(Engine.Lat,Engine.Lon, 10)
'            gmap.MoveCamera(cp)
            'ConvertLatLong
        End If
    Catch
        Log(LastException)
    End Try
 
End Sub


]

In my Geodesy Class (which I initialized as "Geo"), I have the following code:

[/

Sub Class_Globals
    Private Spa As Spatialite_Database

    Dim PI As Double = 3.1415926535897932385
    Dim RADIAN As Double = 2.0 * PI / 360   
   
    Dim Local_srid As String
    'Dim Data_File As String
    Dim spconstants As Spatialite_Constants

'    Initialize(File.DirRootExternal & "/CEASER/Data/" ,Data_File,"READONLY")
'    Spa.Open(File.DirInternal, "mm.sqlite", spconstants.SQLITE_OPEN_READONLY)

End Sub

Public Sub Initialize '(DPATH As String ,DBASE As String , OPEN_STYLE As String)
    Dim DPath, DBase, Open_Style As String

    Spa.Initialize
   
    DPath=File.DirRootExternal & "/CEASER/Data"
    DBase="Projections.sl3"
    Open_Style="ReadOnly"
   
    Spa.Open(DPath, DBase, spconstants.SQLITE_OPEN_READONLY)

    Select Open_Style
        Case "READWRITE"
            Spa.Open(DPath, DBase, spconstants.SQLITE_OPEN_READWRITE)
        Case "READONLY"
            Spa.Open(DPath, DBase, spconstants.SQLITE_OPEN_READONLY)
    End Select
End Sub

Sub get_point_from_local( x As Double , y As Double) As String
    Dim sqStm As Spatialite_TableResult
    Dim result As String
    Dim sql1 As String
   
    sql1 = "SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT("
    sql1 = sql1 & x & " " & y & ")'," & CGlobals.EPSG & "),4326)) As trans_geom; "
    Try
        sqStm = Spa.GetTable(sql1)
        result = sqStm.Rows(0,0)
    Catch
        Log(LastException.Message)
        MsgboxAsync(LastException.Message & CRLF & "wrong EPSG code" ,"ATTENTION")
        Local_srid="2048"
        Return "0000000000000000000"
    End Try
    If result=Null Or result="" Then
        Return "0000000000000000000"
    End If
    Return result
End Sub

'''transform lat lon to local
Sub get_point_to_Local( lat As Double , lon As Double) As String
    Dim sqStm As Spatialite_TableResult
    Dim result As String
    Dim sql1 As String
    sql1 = "SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT("
    sql1 = sql1 & lon & " " & lat & ")',4326),"
    sql1 = sql1 & CGlobals.EPSG & ")) As trans_geom;"
    sqStm = Spa.GetTable(sql1)
    result = sqStm.Rows(0,0)
    Return result
End Sub
]

The EPSG code is correct, as I load it from my Database.

It keeps returning 0, when I want to convert from a Local System to Lats\Longs.

Any Push, please?