Android Question EPSG Codes in SQLite

T

Terradrones

Active Member
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
 
Sort by date Sort by votes
E

emexes

Expert
Licensed User
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?
 
Upvote 0
E

emexes

Expert
Licensed User
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.
Click to expand...

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
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Terradrones said:
When I select "Europe", it tells me that there is no such Column.
Click to expand...
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
 
Last edited:
Upvote 0
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
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
Click to expand...
The main thing (maybe the only thing) I can see wrong is that you have no single quotes around your continent names.

RBS
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
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.
Click to expand...
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
 
Upvote 0
T

Terradrones

Active Member
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
]
 
Upvote 0
E

emexes

Expert
Licensed User
Terradrones said:
I am attaching the File in both CSV and SQLite format.
Click to expand...

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.
 
Upvote 0
E

emexes

Expert
Licensed User
emexes said:
grouped into (seemingly) arbitrarily named grid blocks.
Click to expand...

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 šŸ¤”


1670237001217.png

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

Terradrones

Active Member
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
]
 
Upvote 0
You must log in or register to reply here.

Similar Threads

LucaMs
  • Question
Android Question SQLite SOUNDEX
Replies
11
Views
3K
Mahares
M
W
  • Question
Android Question sqlite "no such function" runtime error
2
Replies
27
Views
6K
Wong Ka Chun
W
M
  • Question
Android Question Continous background GPS tracking
2
Replies
22
Views
9K
arnold steger
A
klaus
  • Article
Android Example SQLiteLight four simple SQLite projects
3 4 5
Replies
89
Views
61K
klaus
klaus
Erel
  • Article
Android Tutorial Android SQLite Viewer
2
Replies
38
Views
59K
roberto64
roberto64
Top