Android Question EPSG Codes in SQLite

Terradrones · Dec 4, 2022

Hi All

I need a push again in the right direction please.

I downloaded all the EPSG codes (over 3600 of them) into a CSV File and from there I imported them into an SQLite Database.

The format is as follows: EPSG Code, Continent, Country & Grid.

If the User clicks on say "Europe", then only the Countries in Europe should show up in the Combobox, if the User clicks on "Africa", then only the Countries in Africa should show up in the Combobox, etc.

I have tried everything, but to no avail. Tried going through the Forum, but also nothing.

Here is my code:

B4X:

[/
Sub cmbContinent_SelectedIndexChanged (Index As Int)
    Dim i As Int

    'Select a Continent
    OpenEPSG
    cmbCountry.cmbBox.clear
    Select Case cmbContinent.SelectedIndex
        Case 0
            'World
            Query = "SELECT * FROM EPSGTable WHERE Continent = World"
        Case 1
            'Europe
            'Query = "SELECT * FROM EPSGTable WHERE Continent = Europe"
            Query="SELECT Country FROM EPSGTable WHERE Continent=Europe"

        Case 2
            'Asia
            Query = "SELECT * FROM EPSGTable WHERE Continent = Asia"

        Case 3
            'North America
            Query = "SELECT * FROM EPSGTable WHERE Continent = North America"

        Case 4
            'South America
            Query = "SELECT * FROM EPSGTable WHERE Continent = South America"

        Case 5
            'Central America
            Query = "SELECT * FROM EPSGTable WHERE Continent = Central America"

        Case 6
            'Africa
            Query = "SELECT * FROM EPSGTable WHERE Continent = Africa"

        Case 7
            'Oceania
            Query = "SELECT * FROM EPSGTable WHERE Continent = Oceania"

    End Select
    Dim Items8 As List
    Items8.Initialize
    ResultSet =CGlobals.SQL1.ExecQuery(Query)    ',Array As String("Europe"))   
    Do While ResultSet.NextRow
        Items8.Add(ResultSet.GetString("Country"))
        ResultSet.NextRow
    Loop
    Dim cs As CSBuilder
    Dim l8 As List
    l8.Initialize
    For i =0 To Items8.Size -1
        l8.Add(cs.Initialize.size(18).Typeface(Typeface.DEFAULT_BOLD).Append(Items8.Get(i)).PopAll)
    Next
    cmbCountry.SetItems(l8)
    ResultSet.Close
End Sub

]

When I select "Europe", it tells me that there is no such Column.

Thank you
Michael

emexes · Dec 4, 2022

Step one is add a log inside the Do While ResultSet.NextRow loop, to confirm you're finding countries you expect to find.

Step two is maybe to select everything, make sure the rest of the system is working.

Step three would be try Query = "SELECT * FROM EPSGTable WHERE Continent LIKE '%America%'"

ie add quotes and use wildcards

also check out whether string matching is case-sensitive eg does America match AMERICA match america match AmErIcA?

emexes · Dec 4, 2022

Terradrones said:
I downloaded all the EPSG codes (over 3600 of them) into a CSV File and from there I imported them into an SQLite Database.

ps that's a pretty nifty resource ?

although I'm currently having trouble working out where it serves up the transformation algorithm so that you can do it offline

Mahares · Dec 4, 2022

Terradrones said:
When I select "Europe", it tells me that there is no such Column.

I think you are taking the wrong approach to the problem. This is how I would do it. Of course code not testd because I do not have your database:

B4X:

Sub cmbContinent_SelectedIndexChanged (Index As Int)
    OpenEPSG  'not sure what this is. It is in your code. You decide
    cmbCountry.cmbBox.clear
    Dim mycontinent As String =cmbContinent.GetItem(Index)
    Dim cs As CSBuilder
    Dim l8 As List
    l8.Initialize
    Dim Query As String =$"SELECT Country FROM EPSGTable WHERE Continent LIKE ?"$
    Dim ResultSet As ResultSet
    ResultSet = SQL.ExecQuery2(Query, Array As String ($"%${mycontinent}%"$))
    Do While ResultSet.NextRow
        l8.Add(cs.Initialize.size(18).Typeface(Typeface.DEFAULT_BOLD).Append(ResultSet.GetString("Country")).PopAll)
    Loop
    cmbCountry.SetItems(l8)
    ResultSet.Close
End Sub

RB Smissaert · Dec 4, 2022

Terradrones said:

Hi All

I need a push again in the right direction please.

I downloaded all the EPSG codes (over 3600 of them) into a CSV File and from there I imported them into an SQLite Database.

The format is as follows: EPSG Code, Continent, Country & Grid.

If the User clicks on say "Europe", then only the Countries in Europe should show up in the Combobox, if the User clicks on "Africa", then only the Countries in Africa should show up in the Combobox, etc.

I have tried everything, but to no avail. Tried going through the Forum, but also nothing.

Here is my code:

B4X:

[/
Sub cmbContinent_SelectedIndexChanged (Index As Int)
    Dim i As Int

    'Select a Continent
    OpenEPSG
    cmbCountry.cmbBox.clear
    Select Case cmbContinent.SelectedIndex
        Case 0
            'World
            Query = "SELECT * FROM EPSGTable WHERE Continent = World"
        Case 1
            'Europe
            'Query = "SELECT * FROM EPSGTable WHERE Continent = Europe"
            Query="SELECT Country FROM EPSGTable WHERE Continent=Europe"

        Case 2
            'Asia
            Query = "SELECT * FROM EPSGTable WHERE Continent = Asia"

        Case 3
            'North America
            Query = "SELECT * FROM EPSGTable WHERE Continent = North America"

        Case 4
            'South America
            Query = "SELECT * FROM EPSGTable WHERE Continent = South America"

        Case 5
            'Central America
            Query = "SELECT * FROM EPSGTable WHERE Continent = Central America"

        Case 6
            'Africa
            Query = "SELECT * FROM EPSGTable WHERE Continent = Africa"

        Case 7
            'Oceania
            Query = "SELECT * FROM EPSGTable WHERE Continent = Oceania"

    End Select
    Dim Items8 As List
    Items8.Initialize
    ResultSet =CGlobals.SQL1.ExecQuery(Query)    ',Array As String("Europe"))  
    Do While ResultSet.NextRow
        Items8.Add(ResultSet.GetString("Country"))
        ResultSet.NextRow
    Loop
    Dim cs As CSBuilder
    Dim l8 As List
    l8.Initialize
    For i =0 To Items8.Size -1
        l8.Add(cs.Initialize.size(18).Typeface(Typeface.DEFAULT_BOLD).Append(Items8.Get(i)).PopAll)
    Next
    cmbCountry.SetItems(l8)
    ResultSet.Close
End Sub

]

When I select "Europe", it tells me that there is no such Column.

Thank you
Michael

The main thing (maybe the only thing) I can see wrong is that you have no single quotes around your continent names.

RBS

Mahares · Dec 4, 2022

RB Smissaert said:
The main thing (maybe the only thing) I can see wrong is that you have no single quotes around your continent names.

Look again. Look at this below code the OP has. It is not correct. Did you miss it?

B4X:

Do While ResultSet.NextRow
        Items8.Add(ResultSet.GetString("Country"))
        ResultSet.NextRow
    Loop

RB Smissaert · Dec 4, 2022

Mahares said:
Look again. Look at this below code the OP has. It is not correct. Did you miss it?

B4X:

Do While ResultSet.NextRow Items8.Add(ResultSet.GetString("Country")) ResultSet.NextRow Loop

Yes, indeed.

RBS

Terradrones · Dec 5, 2022

The above is still not working.

I am attaching the File in both CSV and SQLite format.

Terradrones · Dec 5, 2022

Hi All

I found my mistake.

My Database was empty!

I use "OpenEPSG" to drop the existing Table before I import the new data from the CSV file.

B4X:

[/
Sub OpenEPSG
    If File.Exists(File.DirRootExternal  & "/CEASER/DATA" , "EPSG" & ".sl3") = False Then
        CGlobals.SQL1.Initialize(File.DirRootExternal  & "/CEASER/DATA" , "EPSG" & ".sl3", True)
        'CreateEPSGTable
    Else
        CGlobals.SQL1.Initialize(File.DirRootExternal  & "/CEASER/DATA" , "EPSG" & ".sl3", False)
        'CreateEPSGTable
    End If
End Sub

Sub CreateEPSGTable
    CGlobals.SQL1.ExecNonQuery("DROP TABLE IF EXISTS EPSGTable")
    Query = "CREATE TABLE IF NOT EXISTS EPSGTable (EPSG TEXT, Continent TEXT, Country TEXT, Grid TEXT)"
    CGlobals.SQL1.ExecNonQuery(Query)
    Sleep(0)
End Sub
]

I fixed it up and it is working now.

[CODE=b4x][/
    OpenEPSG
    cmbCountry.cmbBox.clear
    Query = "SELECT * FROM EPSGTable WHERE Continent = ?"
    Select Case cmbContinent.SelectedIndex
        Case 0
            'World
            Continent="World"
        Case 1
            'Europe
            Continent="Europe"

        Case 2
            'Asia
            Continent="Asia"

        Case 3
            'North America
            Continent="North America"

        Case 4
            'South America
            Continent="South America"

        Case 5
            'Central America
            Continent="Central America"

        Case 6
            'Africa
            Continent="Africa"

        Case 7
            'Oceania
            Continent="Oceania"

    End Select
    Dim Items8 As List
    Items8.Initialize
    ResultSet =CGlobals.SQL1.ExecQuery2(Query,Array As String(Continent))
    Do While ResultSet.NextRow
        Items8.Add(ResultSet.GetString("Country"))
        ResultSet.NextRow
    Loop
    Dim cs As CSBuilder
    Dim l8 As List
    l8.Initialize
    For i =0 To Items8.Size -1
        l8.Add(cs.Initialize.size(18).Typeface(Typeface.DEFAULT_BOLD).Append(Items8.Get(i)).PopAll)
    Next
    cmbCountry.SetItems(l8)
    ResultSet.Close
]

emexes · Dec 5, 2022

Terradrones said:
I am attaching the File in both CSV and SQLite format.

Presumably the entire 3600 would be too large to attach, but if you could just attach say the most popular/common half-dozen or so, I'd be interested to see how the coordinate transformations are specified. In particular, when the coordinates are alphanumeric, or grouped into (seemingly) arbitrarily named grid blocks.

emexes · Dec 5, 2022

emexes said:
grouped into (seemingly) arbitrarily named grid blocks.

Maybe I've misunderstood what this data represents.

I thought it was to translate from one location system to another. In Melbourne, before GPS became a thing, it was normal to give a Melways map reference, but now I've found there's no entry at epsg.io or spatialreference.org for translating Melways <--> Lat-Lon ?

https://www.feetonforeignlands.com/2007/11/07/b4-you-go-go-melways-map-58/

emexes · Dec 5, 2022

Is this the kind of coordinate "polyglotness" that you're implementing in your app? (albeit here only 5 systems cf your 3600+) :

http://support.cop.em.vic.gov.au/navigating-the-map/

Terradrones · Dec 5, 2022

Hi
Gvoulg showed me an example using Spatialite with the EPSG codes to convert from Lat\Long to any Coordinate system and vice versa.

In my Database I have the EPSG codes for most Countries. Emexes gave me a link to more EPSG Codes and I will update my Database soon.

If you give me your email address, I can forward you the Codes.

Herewith my coding with the help of Gvoulg (still testing it):

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
    
    DPath=File.DirRootExternal & "/CEASER/Data"
    DBase="Projections"
    Open_Style="ReadOnly"
    
    Spa.Open(File.DirRootExternal, "EPSG.sl3", spconstants.SQLITE_OPEN_READONLY)

    Spa.Initialize
    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 & ")'," & Local_srid & "),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="2100"
        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 & Local_srid & ")) As trans_geom;"
    sqStm = Spa.GetTable(sql1)
    result = sqStm.Rows(0,0)
    Return result
End Sub
]

Terradrones · Dec 14, 2022

Hi

The above is not working for me.

I need more help please.

Android Question EPSG Codes in SQLite

Terradrones

Active Member

emexes

Expert

emexes

Expert

Mahares

Expert

RB Smissaert

Well-Known Member

Mahares

Expert

RB Smissaert

Well-Known Member

Terradrones

Active Member

Terradrones

Active Member

emexes

Expert

emexes

Expert

emexes

Expert

Terradrones

Active Member

Terradrones

Active Member

Similar Threads