sábado, 24 de junho de 2017

Create your own Radio Station using IceCast



There are a lot of options to start your own Radio Station right?
The answer is. Yes but...

What king of flawor your are looking for?
A big Station, cheap?

Ok, as you can see bellow you can compare with you self a brief list of stations.

Refer.
I would like to recommend please, first read the wiki bellow:

https://en.wikipedia.org/wiki/Comparison_of_streaming_media_systems


Ok, what is Ice Cast and why we are talking about him?


I need to take a brief decision in 2 weeks to start my own station, and a good friend use this station a serveral years.

Hands On!


"Icecast is a streaming media project released as free software maintained by the Xiph.org Foundation. It also refers specifically to the server program which is part of the project. Icecast was created in December 1998/January 1999 by Jack Moffitt[2][3] and Barath Raghavan[3] to provide an open source audio streaming server that anyone could modify, use, and tinker with. Version 2 was started in 2001, a ground-up rewrite aimed at multi-format support (initially targeting Ogg Vorbis) and scalability; this rewrite was released in January 2004."

Refer: https://en.wikipedia.org/wiki/Icecast



......


I still working during this month in this topic....
.....





sexta-feira, 23 de junho de 2017

Convert Shapefile to kml in QGIS

*KML is a space XML file, only.

Keyhole Markup Language (KML) is an XML notation for expressing geographic annotation and visualization within Internet-based, two-dimensional maps and three-dimensional Earth browsers. KML was developed for use with Google Earth, which was originally named Keyhole Earth Viewer. It was created by Keyhole, Inc, which was acquired by Google in 2004. KML became an international standard of the Open Geospatial Consortium in 2008.[1][2] Google Earth was the first program able to view and graphically edit KML files. Other projects such as Marble have also started to develop KML support.[3]
 Refer: Wiki https://en.wikipedia.org/wiki/Keyhole_Markup_Language



1) Download Shape File Ex: http://ippuc.org.br/geodownloads/SHAPES/DIVISA_DE_REGIONAIS.zip

Convert Shapefile to kml in QGIS:

 https://i2.wp.com/www.igismap.com/wp-content/uploads/2014/06/Convert-Shapefile-to-kml-by-QGIS.png



2) More Details (here) http://www.igismap.com/convert-shapefile-kml-qgis/ .



3) Open KML and add the following data for each point..



 -49.2647406360701,-25.5264678303564 49.2644644077967,-25.5266076506249

change to

  -49.2647406360701,-25.5264678303564,0.000000 -49.2644644077967,-25.5266076506249,0.000000

This a mandatory step, without the cordinate Z LeaFlet(http://leafletjs.com/) will not work.


terça-feira, 23 de maio de 2017

OSM to Garmin

Garmin is probably the biggest manufacturer of GPS devices around.
"Mkgmap is a command line utility that converts OpenStreetMap data into vector maps that can be loaded onto a Garmin GPS device. It does the conversion in one step without depending on any other program. Mkgmap only supports the OSM map data format. "





http://www.mkgmap.org.uk/download/mkgmap.html 


Creating a map

Creating a map consists of several steps.
  1. Download OSM data
  2. Split the OSM data into tile using the tile splitter
  3. Create your own map style files (optional)
  4. Compile the tiles with mkgmap
  5. Upload the map to your GPS



 References:
http://wiki.openstreetmap.org/wiki/Mkgmap on 23/05/2017

segunda-feira, 22 de maio de 2017

OSM - How to Find Adddress from OSM Database. C#

What type of question you want to answeer?

I´m hungry! Jade, show me the best option with U$ 6 dollars....



     


select id,Latitude,Longitude,Endereco, Numero,
                                (
                                case
                                                                   when Numero = '1771' then '1'
                                                                   else 0
                                                           end
                                                           ) as Calculado5, CEP, Complemento, Bairro, Cidade, UF from (
                                                select  distinct
                                                            MAX(admin_93.place_id)  as id
                                                       , 'Brasil'               as Pais
                                                       , admin_100.name->'ref' as UF
                                                       , admin_96.name->'name' as Cidade
                                                        , admin_95.name->'name' as Bairro
                                                        , admin_94.name->'name' as Endereco                                          
                                                        , admin_93.name->'name'  as Complemento
                                                               , avg(ST_X(admin_93.centroid))  as Longitude
                                                                , avg(ST_Y(admin_93.centroid))  as Latitude      
                                                               , admin_93.postcode  as CEP
                                                               ,regexp_replace(admin_93.housenumber, '[^0-9]', '', 'g') as Numero
                                                                                                     
                                                       from  placex admin_100
                                                        inner join  placex  admin_99
                                                        on  admin_99.parent_place_id=admin_100.place_id
                                                        and admin_100.calculated_country_code='br' and admin_100.rank_search in(8)
                                                        and unaccent(lower(admin_100.name->'name'))
                                                           like unaccent(lower('%Paraná%'))
                                                        and  unaccent(lower(admin_99.name->'name'))
                                                           like unaccent(lower('%Mesorregião Metropolitana de Curitiba%'))
                                                        inner join  placex  admin_98 
                                                        on  admin_98.parent_place_id=admin_99.place_id
                                                       and  unaccent(lower(admin_98.name->'name'))
                                                           like unaccent(lower('%Região Metropolitana de Curitiba%'))
                                                        inner join  placex  admin_97 
                                                        on  admin_97.parent_place_id=admin_98.place_id
                                                       and  unaccent(lower(admin_97.name->'name'))
                                                           like unaccent(lower('%Microrregião de Curitiba%'))
                                                       inner join  placex  admin_96 
                                                        on  admin_96.parent_place_id=admin_97.place_id
                                                       and  unaccent(lower(admin_96.name->'name'))
                                                           like unaccent(lower('%CURITIBA%'))
                                                       left outer join  placex  admin_95
                                                        on  admin_95.parent_place_id=admin_96.place_id

                                                       left outer join  placex  admin_94 
                                                        on  admin_94.parent_place_id=admin_95.place_id
                                                           --where admin_95.class='highway'

                                                       left outer join  placex  admin_93 
                                                        on  admin_93.parent_place_id=admin_94.place_id
                                                           --where admin_95.class='highway'

                                           
                                                        group by UF,Cidade,Bairro,Endereco,CEP,Complemento,Numero
                                                   ) as rpt
                                                   where   ( unaccent(lower(Endereco))  like unaccent(lower('%FATIMA%BARK%')) or unaccent(lower(Complemento))  like unaccent(lower('%FATIMA%BARK%')) ) and  (   (
                                                (cast(
                                                                               case
                                                                                   when Numero is null then '0'

                                                    when Numero = '' then '0'
                                                                                   else Numero

                                                    end
                                                    as int
                                                ) between 0 and 1771  and Numero <> '')
                                                                           
                                                                   )) and  unaccent(lower(Bairro))  like unaccent(lower('PINHEIRINHO%'))    and id is not null
                                order by Endereco, Numero desc  limit 1;



namespace Combinacao
{
    class Program
    {
        public static List<string> Combinacoes = new List<string>();

        static void Main(string[] args)
        {
            string strWords = "rua teste 001";
            Console.WriteLine(BuildQuery(strWords));
            Console.ReadLine();
        }

        static string BuildQuery(string strWords)
        {
            List<string> permutas = new List<string>();

            for (int i = 0; i < strWords.Split(' ').Length; i++)
            {
                permutas.AddRange(Words(strWords.TrimEnd(), i + 1));
            }

            string strIN = string.Empty, strVirgula = string.Empty;
            for (int i = 0; i < permutas.Count; i++)
            {

                strIN += strVirgula + "'" + permutas[i].Trim() + "'";
                strVirgula = ",";
                strIN += strVirgula + "' " + permutas[i].Trim() + "'";

            }
            //TODO Gerar para Likes...
            //permutacao de chars
            string strSQL = "select word_id, word_token, word, class, type, country_code, operator, search_name_count from word where word_token in (" + strIN + ")";
            return strSQL;
        }
        static List<string> Words(string phrase, int i) {
            List<string> results = new List<string>();
            var result = GetPermutations(phrase.Split(' '), i);
            foreach (var perm in result)
            {
                string word = string.Empty;
                foreach (var c in perm)
                {
                    word += c + " ";
                }
                results.Add(word);
            }
            return results;
        }
        static IEnumerable<IEnumerable<T>> GetPermutations<T>(IEnumerable<T> items, int count)
        {
            int i = 0;
            foreach (var item in items)
            {
                if (count == 1)
                    yield return new T[] { item };
                else
                {
                    foreach (var result in GetPermutations(items.Skip(i + 1), count - 1))
                        yield return new T[] { item }.Concat(result);
                }

                ++i;
            }
        }
    }
}

Database Directly

SELECT p.* FROM (SELECT id, unnest(tags) tag FROM planet_osm_ways) x 
INNER JOIN planet_osm_ways p 
on p. id=x.id 
WHERE tag LIKE '%José%Domakoski%' 



select * from planet_osm_nodes where id in( 
324368910,324368959,324368914,324368916,324368917,324368918,324358182,1843398431,1843398436,1843398418,1843398440,1370740758,269215762 

)


Similar Words

LevenshteinDistance 
DoubleMetaphone 
Lucene.NET 





elect 
osm_type 
, osm_id 
, class 
, type 
, admin_level 
, rank_search 
, rank_address 
, min(place_id) as place_id 
, min(parent_place_id) as parent_place_id, calculated_country_code as country_code 
, get_address_by_language(place_id, ARRAY['short_name:pt-BR','short_name:pt','short_name:en-US','short_name:en','name:pt-BR','name:pt','name:en-US','name:en','place_name:pt-BR','place_name:pt','place_name:en-US','place_name:en','official_name:pt-BR','official_name:pt','official_name:en-US','official_name:en','short_name','name','place_name','official_name','ref','type']) as langaddress 
, get_name_by_language(name, ARRAY['short_name:pt-BR','short_name:pt','short_name:en-US','short_name:en','name:pt-BR','name:pt','name:en-US','name:en','place_name:pt-BR','place_name:pt','place_name:en-US','place_name:en','official_name:pt-BR','official_name:pt','official_name:en-US','official_name:en','short_name','name','place_name','official_name','ref','type']) as placename 
, get_name_by_language(name, ARRAY['ref']) as ref 
, avg(ST_X(centroid)) as lon 
, avg(ST_Y(centroid)) as lat 
, coalesce(importance,0.75-(rank_search::float/40)) as importance 
, (select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(CASE WHEN placex.rank_search < 28 THEN placex.place_id ELSE placex.parent_place_id END) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance 
, (extratags->'place') as extra_place 
from placex
where place_id in (521255) and (placex.rank_address between 0 and 30 
OR (extratags->'place') = 'city') and linked_place_id is null 
group by 
osm_type,osm_id,class,type,admin_level,rank_search,rank_address,calculated_country_code 
,importance,langaddress ,placename ,ref ,extratags->'place' 

union 

select 'T' as osm_type 
,place_id as osm_id 
,'place' as class 
,'house' as type 
,null as admin_level 
,30 as rank_search 
,30 as rank_address 
,min(place_id) as place_id 
, min(parent_place_id) as parent_place_id 
,'us' as country_code 
,get_address_by_language(place_id, ARRAY['short_name:pt-BR','short_name:pt','short_name:en-US','short_name:en','name:pt-BR','name:pt','name:en-US','name:en','place_name:pt-BR','place_name:pt','place_name:en-US','place_name:en','official_name:pt-BR','official_name:pt','official_name:en-US','official_name:en','short_name','name','place_name','official_name','ref','type']) as langaddress 
,null as placename 
,null as ref 
,avg(ST_X(centroid)) as lon 
,avg(ST_Y(centroid)) as lat 
, -1.15 as importance 
, (select max(p.importance*(p.rank_address+2)) from place_addressline s 
, placex p where s.place_id = min(location_property_tiger.parent_place_id) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance 
, null as extra_place from location_property_tiger 

where place_id in (521255) and 30 between 0 and 30 
group by place_id 

union 

select 

'L' as osm_type 
,place_id as osm_id 
,'place' as class 
,'house' as type 
,null as admin_level 
,30 as rank_search 
,30 as rank_address 
,min(place_id) as place_id 
, min(parent_place_id) as parent_place_id 
,'us' as country_code 
,get_address_by_language(place_id, ARRAY['short_name:pt-BR','short_name:pt','short_name:en-US','short_name:en','name:pt-BR','name:pt','name:en-US','name:en','place_name:pt-BR','place_name:pt','place_name:en-US','place_name:en','official_name:pt-BR','official_name:pt','official_name:en-US','official_name:en','short_name','name','place_name','official_name','ref','type']) as langaddress 
,null as placename 
,null as ref 
,avg(ST_X(centroid)) as lon 
,avg(ST_Y(centroid)) as lat 
, -1.10 as importance 
, (select max(p.importance*(p.rank_address+2)) from place_addressline s, placex p where s.place_id = min(location_property_aux.parent_place_id) and p.place_id = s.address_place_id and s.isaddress and p.importance is not null) as addressimportance 
, null as extra_place from location_property_aux 

where place_id in (521255) and 30 between 0 and 30 

group by place_id,get_address_by_language(place_id, ARRAY['short_name:pt-BR','short_name:pt','short_name:en-US','short_name:en','name:pt-BR','name:pt','name:en-US','name:en','place_name:pt-BR','place_name:pt','place_name:en-US','place_name:en','official_name:pt-BR','official_name:pt','official_name:en-US','official_name:en','short_name','name','place_name','official_name','ref','type']) 

order by importance desc



select 
name->'name' as name 
, avg(ST_X(centroid)) as lon 
, avg(ST_Y(centroid)) as lat 
from placex where calculated_country_code='br' and rank_search in( 26,27,28) 
and name->'name' like '%Domakoski%' 
group by name 
order by name asc




select id,Latitude,Longitude,Endereco, Complemento, Bairro, Cidade, UF from (
                                    select  distinct
                                      MAX(admin_09.place_id) as id
                                    , 'Brasil'               as Pais
                                    , admin_5.name->'name'   as UF
                                    , admin_1.name->'name'   as Cidade
                                    , admin_0.name->'name'   as Bairro
                                    , admin_09.name->'name' as Endereco
                                            , '' as Complemento
                                    , avg(ST_X(admin_09.centroid))  as Longitude
                                    , avg(ST_Y(admin_09.centroid))  as Latitude
                                    from  placex admin_5
                                    inner join  placex  admin_4
                                    on  admin_4.parent_place_id=admin_5.place_id
                                    and admin_5.calculated_country_code='br' and admin_5.rank_search in(8)
                                    and admin_5.name->'name' like '%Paraná%' and  admin_4.name->'name'   like '%Curitiba%'

                                    inner join  placex admin_3
                                    on  admin_3.parent_place_id=admin_4.place_id and  admin_3.name->'name' like '%Curitiba%'

                                    inner join  placex admin_2
                                    on  admin_2.parent_place_id=admin_3.place_id and  admin_2.name->'name' like '%Curitiba%'

                                    inner join  placex admin_1
                                    on  admin_1.parent_place_id=admin_2.place_id and  admin_1.name->'name' like '%Curitiba%'

                                    inner join  placex admin_0
                                    on  admin_0.parent_place_id =admin_1.place_id

                                    inner join  placex admin_09
                                    on  admin_09.parent_place_id =admin_0.place_id

                                    group by UF,Cidade,Bairro,Endereco
                                    ) as rpt
                                    where   lower(Endereco)  like lower('%Tiradentes%')
                                    order by Endereco
 




MORE FAST.


select id,Latitude,Longitude,Endereco, Numero, CEP, Complemento, Bairro, Cidade, UF from (
                                    select  distinct
                                      MAX(admin_09.place_id) as id
                                    , 'Brasil'               as Pais
                                    , admin_5.name->'name'   as UF
                                    , admin_1.name->'name'   as Cidade
                                    , admin_0.name->'name'   as Bairro
                                    , admin_09.name->'name' as Endereco                                          
                                    , admin_10.name->'name'  as Complemento
                                    , avg(ST_X(admin_09.centroid))  as Longitude
                                    , avg(ST_Y(admin_09.centroid))  as Latitude      
                                    , admin_10.postcode  as CEP
                                    , admin_10.housenumber  as Numero
                                   
                                    from  placex admin_5
                                    inner join  placex  admin_4
                                    on  admin_4.parent_place_id=admin_5.place_id
                                    and admin_5.calculated_country_code='br' and admin_5.rank_search in(8)
                                    and admin_5.name->'name' like '%Paraná%' and  admin_4.name->'name'   like '%Curitiba%'
                                    inner join  placex admin_3
                                    on  admin_3.parent_place_id=admin_4.place_id and  admin_3.name->'name' like '%Curitiba%'
                                    inner join  placex admin_2
                                    on  admin_2.parent_place_id=admin_3.place_id and  admin_2.name->'name' like '%Curitiba%'
                                    inner join  placex admin_1
                                    on  admin_1.parent_place_id=admin_2.place_id and  admin_1.name->'name' like '%Curitiba%'
                                    inner join  placex admin_0
                                    on  admin_0.parent_place_id =admin_1.place_id
                                    inner join  placex admin_09
                                    on  admin_09.parent_place_id =admin_0.place_id

                                    left outer join  placex admin_10
                                    on  admin_10.parent_place_id =admin_09.place_id

                                    left outer join  placex admin_11
                                    on  admin_11.parent_place_id =admin_10.place_id

                     
              
                                    group by UF,Cidade,Bairro,Endereco,CEP,Complemento,Numero
                                    ) as rpt
                                    where   lower(CEP)  like lower('%80035%020%')           
      





  public static Geo[] PesquisarGeoOSM(string Endereco, string Numero, string CEP, string Cidade, string UF)
        {
            Logger.Gravar("PesquisarGeoOSM (Endereco){" + Endereco + "} - (Numero) - {" + Numero + "} - (CEP) {" + CEP + "} - (Cidade) {" + Cidade + "} - (UF) {" + UF + "}");
            System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(Util.BaseOSM());

            string Bairro = string.Empty;
            Endereco = String.IsNullOrEmpty(Endereco) ? string.Empty : Endereco.Replace(" ", "%");
            Cidade = String.IsNullOrEmpty(Cidade) ? string.Empty : Cidade.Replace(" ", "%");
            UF = String.IsNullOrEmpty(UF) ? string.Empty : UF.Replace(" ", "%");

            /*
            http://wiki.openstreetmap.org/wiki/Nominatim/Development_overview#Country_to_street_level
            For administrative boundaries:
            admin_level * 2    4–22
            Continent, sea     2
            Country     4
            State       8
            Region      10
            County      12
            City        16
            Island, town, moor, waterways      17
            Village, hamlet, municipality, district, borough, airport, national park 18
            Suburb, croft, subdivision, farm, locality, islet 20
            Hall of residence, neighbourhood, housing estate, landuse (polygon only) 22
            Airport, street, road      26
            Paths, cycleways, service roads, etc.      27
            House, building    28
            Postcode    11–25 (depends on country)
            Other       30
             */

            string strQuery = string.Empty;

            #region Validação
            string strError = string.Empty;
            if (Cidade == string.Empty || (UF == string.Empty))
            {
                strError = "Campo obrigatório: Cidade/UF.";
                Logger.Gravar(strError);
                throw new Exception(strError);
            }

            if (Endereco == string.Empty && CEP == string.Empty)
            {
                strError = "Campo obrigatório: Endereço ou CEP.";
                Logger.Gravar(strError);
                throw new Exception(strError);
            }

            if (Endereco != string.Empty && Endereco.Length < 3)
            {
                strError = ("Endereço deve ter no mínimo 3 caracteres.");
                Logger.Gravar(strError);
                throw new Exception(strError);
            }
            if(!String.IsNullOrEmpty(CEP))
                CEP = CEP.Trim();
            #endregion

            string strWhere = string.Empty;
            string strWhereAnd = string.Empty;

            if (!String.IsNullOrEmpty(CEP))
            {

                CEP = CEP.Replace("-", "%");

                strWhere += strWhereAnd + " CEP  like '%" + CEP + @"%' ";
                strWhereAnd = " and ";
            }
            else
            {
                if (!String.IsNullOrEmpty(Endereco))
                {
                    strWhere += strWhereAnd + " lower(Endereco)  like lower('%" + Endereco + @"%') ";
                    strWhereAnd = " and ";
                }

                if (!String.IsNullOrEmpty(Numero))
                {
                    strWhere += strWhereAnd + " Numero  like '" + Numero + @"%' ";
                    strWhereAnd = " and ";
                }
            }
            if (!String.IsNullOrEmpty(strWhere))
            {
                //Todo falta case insensite acente insensitive
                strQuery += @"select id,Latitude,Longitude,Endereco, Numero, CEP, Complemento, Bairro, Cidade, UF from (
                                            select  distinct
                                                     MAX(admin_09.place_id) as id
                                                    , 'Brasil'               as Pais
                                                    , admin_5.name->'name'   as UF
                                                    , admin_1.name->'name'   as Cidade
                                                    , admin_0.name->'name'   as Bairro
                                                    , admin_09.name->'name' as Endereco                                          
                                                    , admin_10.name->'name'  as Complemento
                                                    , avg(ST_X(admin_09.centroid))  as Longitude
                                                    , avg(ST_Y(admin_09.centroid))  as Latitude      
                                                    , admin_10.postcode  as CEP
                                                    , admin_10.housenumber  as Numero
                                            from  placex admin_5
                                            inner join  placex  admin_4
                                            on  admin_4.parent_place_id=admin_5.place_id
                                            and admin_5.calculated_country_code='br' and admin_5.rank_search in(8)
                                            and admin_5.name->'name' like '%" + UF + "%' and  admin_4.name->'name'   like '%" + Cidade + @"%'

                                            inner join  placex admin_3
                                            on  admin_3.parent_place_id=admin_4.place_id and  admin_3.name->'name' like '%" + Cidade + @"%'

                                            inner join  placex admin_2
                                            on  admin_2.parent_place_id=admin_3.place_id and  admin_2.name->'name' like '%" + Cidade + @"%'

                                            inner join  placex admin_1
                                            on  admin_1.parent_place_id=admin_2.place_id and  admin_1.name->'name' like '%" + Cidade + @"%'

                                            inner join  placex admin_0 " + (Bairro != string.Empty ? "and  admin_0.name->'name' like '%" + Bairro + @"%" : string.Empty) + @"
                                            on  admin_0.parent_place_id =admin_1.place_id

                                            inner join  placex admin_09
                                            on  admin_09.parent_place_id =admin_0.place_id

                                        left outer join  placex admin_10
                                        on  admin_10.parent_place_id =admin_09.place_id

                                        left outer join  placex admin_11
                                        on  admin_11.parent_place_id =admin_10.place_id

                                            group by UF,Cidade,Bairro,Endereco,CEP,Complemento,Numero
                                            ) as rpt
                                            where " + strWhere + @"
                                            order by Endereco"; //TODO falta tirar os acentos
            }


            DataTable odt = new DataTable();
            try
            {
                con.Open();
                System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, con);
                adapter.Fill(odt);

                Geo[] oList = new Geo[odt.Rows.Count];
                int index = 0;
                foreach (DataRow oRow in odt.Rows)
                {
                    Geo oObjeto = new Geo();
                    oObjeto.LigarDados(oRow);
                    oList.SetValue(oObjeto, index++);
                }
                if (oList.Length <= 0)
                {

                    Logger.Gravar("Não encontrado PesquisarGeoOSM (Endereco){" + Endereco + "} - (Numero) - {" + Numero + "} - (CEP) {" + CEP + "} - (Cidade) {" + Cidade + "} - (UF) {" + UF + "}", Util.ArquivoLog() + "_query_OSM2_PesquisarGeoOSM.txt");
                }
                return oList;
            }
            catch (Exception ex)
            {
                Logger.Gravar("Erro ao acessar a url PesquisarGeoOSM: " + ex.Message);
                return new Geo[0];
            }
            finally
            {
                if (con.State != ConnectionState.Closed)
                    con.Close();
            }
        }



        #region LigarDados
        protected void LigarDados(DataRow oRow)
        {
            Id = Convert.ToString(oRow["id"]);
            Cidade = Convert.ToString(oRow["Cidade"]);
            Bairro = Convert.ToString(oRow["Bairro"]);
            Endereco = Convert.ToString(oRow["Endereco"]);
            Complemento = Convert.ToString(oRow["Complemento"]);
            Latitude = Convert.ToString(oRow["Latitude"]);
            Longitude = Convert.ToString(oRow["Longitude"]);

            Numero = Convert.ToString(GetValue(oRow, "Numero"));
            CEP = Convert.ToString(GetValue(oRow, "CEP"));


        }
        public object GetValue(DataRow row, string column)
        {
            return row.Table.Columns.Contains(column) ? row[column] : null;
        }
        #endregion


public static Geo[] PesquisarGeoOSM(string Endereco, string Numero, string CEP, string Bairro, string Cidade, string UF, string Complemento)

        {

            Logger.Gravar("PesquisarGeoOSM (Endereco){" + Endereco + "} - (Numero) - {" + Numero + "} - (CEP) {" + CEP + "} - (Cidade) {" + Cidade + "} - (UF) {" + UF + "}");

            System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(Util.BaseOSM());



           

            Endereco = String.IsNullOrEmpty(Endereco) ? string.Empty : Endereco.Replace(" ", "%");

            Cidade = String.IsNullOrEmpty(Cidade) ? string.Empty : Cidade.Replace(" ", "%");

            UF = String.IsNullOrEmpty(UF) ? string.Empty : UF.Replace(" ", "%");



            /*

            http://wiki.openstreetmap.org/wiki/Nominatim/Development_overview#Country_to_street_level

            For administrative boundaries:

            admin_level * 2       4–22

            Continent, sea        2

            Country        4

            State   8

            Region 10

            County 12

            City    16

            Island, town, moor, waterways      17

            Village, hamlet, municipality, district, borough, airport, national park   18

            Suburb, croft, subdivision, farm, locality, islet      20

            Hall of residence, neighbourhood, housing estate, landuse (polygon only)   22

            Airport, street, road       26

            Paths, cycleways, service roads, etc.     27

            House, building       28

            Postcode       11–25 (depends on country)

            Other   30

             */



            string strQuery = string.Empty;



            #region Validação

            string strError = string.Empty;

            if (Cidade == string.Empty || (UF == string.Empty))

            {

                strError = "Campo obrigatório: Cidade/UF.";

                Logger.Gravar(strError);

                throw new Exception(strError);

            }



            if (Endereco == string.Empty && CEP == string.Empty)

            {

                strError = "Campo obrigatório: Endereço ou CEP.";

                Logger.Gravar(strError);

                throw new Exception(strError);

            }



            if (Endereco != string.Empty && Endereco.Length < 3)

            {

                strError = ("Endereço deve ter no mínimo 3 caracteres.");

                Logger.Gravar(strError);

                throw new Exception(strError);

            }

            if(!String.IsNullOrEmpty(CEP))

                CEP = CEP.Trim();

            #endregion



            string strWhere = string.Empty;

            string strWhereAnd = string.Empty;



            if (!String.IsNullOrEmpty(CEP))

            {



                CEP = CEP.Replace("-", "%");



                strWhere += strWhereAnd + " CEP  like '%" + CEP + @"%' ";

                strWhereAnd = " and ";

            }

            else

            {

                if (!String.IsNullOrEmpty(Endereco))

                {

                    strWhere += strWhereAnd + " unaccent(lower(Endereco))  like unaccent(lower('%" + Endereco + @"%')) ";

                    strWhereAnd = " and ";

                }



                if (!String.IsNullOrEmpty(Numero))

                {

                    strWhere += strWhereAnd + " Numero  like '" + Numero + @"%' ";

                    strWhereAnd = " and ";

                }



                if (!String.IsNullOrEmpty(Complemento))

                {

                    strWhere += strWhereAnd + " unaccent(lower(Complemento))  like unaccent(lower('" + Complemento + @"%')) ";

                    strWhereAnd = " and ";

                }

            }

            if (!String.IsNullOrEmpty(strWhere))

            {

                //Todo falta case insensite acente insensitive

                strQuery += @"select id,Latitude,Longitude,Endereco, Numero, CEP, Complemento, Bairro, Cidade, UF from (

                                           select  distinct

                                                  MAX(admin_09.place_id) as id

                                                    , 'Brasil'               as Pais

                                                    , admin_5.name->'name'   as UF

                                                    , admin_1.name->'name'   as Cidade

                                                    , admin_0.name->'name'   as Bairro

                                                    , admin_09.name->'name' as Endereco                                          

                                                    , admin_10.name->'name'  as Complemento

                                                    , avg(ST_X(admin_09.centroid))  as Longitude

                                                    , avg(ST_Y(admin_09.centroid))  as Latitude      

                                                    , admin_10.postcode  as CEP

                                                    , admin_10.housenumber  as Numero

                                           from  placex admin_5

                                           inner join  placex  admin_4

                                           on  admin_4.parent_place_id=admin_5.place_id

                                           and admin_5.calculated_country_code='br' and admin_5.rank_search in(8)

                                           and admin_5.name->'name' like '%" + UF + "%' and  unaccent(lower(admin_4.name->'name')) like unaccent(lower('%" + Cidade + @"%'))



                                           inner join  placex admin_3

                                           on  admin_3.parent_place_id=admin_4.place_id and  unaccent(lower(admin_3.name->'name')) like unaccent(lower('%" + Cidade + @"%'))



                                           inner join  placex admin_2

                                           on  admin_2.parent_place_id=admin_3.place_id and  unaccent(lower(admin_2.name->'name')) like unaccent(lower('%" + Cidade + @"%'))



                                           inner join  placex admin_1

                                           on  admin_1.parent_place_id=admin_2.place_id and  unaccent(lower(admin_1.name->'name')) like unaccent(lower('%" + Cidade + @"%'))



                                           inner join  placex admin_0

                                           on  admin_0.parent_place_id =admin_1.place_id " + (Bairro != string.Empty ? "and  unaccent(lower(admin_0.name->'name')) like unaccent(lower('%" + Bairro + @"%'))" : string.Empty) + @"



                                           inner join  placex admin_09

                                           on  admin_09.parent_place_id =admin_0.place_id



                                        left outer join  placex admin_10

                                        on  admin_10.parent_place_id =admin_09.place_id



                                        left outer join  placex admin_11

                                        on  admin_11.parent_place_id =admin_10.place_id



                                           group by UF,Cidade,Bairro,Endereco,CEP,Complemento,Numero

                                           ) as rpt

                                           where " + strWhere + @"

                                           order by Endereco"; //TODO falta tirar os acentos

            }





            DataTable odt = new DataTable();

            try

            {

                con.Open();

                System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, con);

                adapter.Fill(odt);



                Geo[] oList = new Geo[odt.Rows.Count];

                int index = 0;

                foreach (DataRow oRow in odt.Rows)

                {

                    Geo oObjeto = new Geo();

                    oObjeto.LigarDados(oRow);

                    oList.SetValue(oObjeto, index++);

                }

                if (oList.Length <= 0)

                {



                    Logger.Gravar("Não encontrado PesquisarGeoOSM (Endereco){" + Endereco + "} - (Numero) - {" + Numero + "} - (CEP) {" + CEP + "} - (Cidade) {" + Cidade + "} - (UF) {" + UF + "}", Util.ArquivoLog() + "_query_OSM2_PesquisarGeoOSM.txt");

                }

                return oList;

            }

            catch (Exception ex)

            {

                Logger.Gravar("Erro ao acessar a url PesquisarGeoOSM: " + ex.Message);

                return new Geo[0];

            }

            finally

            {

                if (con.State != ConnectionState.Closed)

                    con.Close();

            }

        }