Android Question Spatialite

Terradrones

Active Member
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

Active Member
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:

B4X:
[/
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:

B4X:
[/

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?
 
Upvote 0
Top