3. Johannes (DeatMeat) had the idea and the source (geodata.org)... Add geodata to ADempiere. I did a table and the import process to fill it with data from geonames.org (http://download.geonames.org/export/dump/). They could be used for things like - autofill the city name for entered zip code (or the other way round) - generate a google map with all your customers - use it for reporting ...
So what do you think?
Regards,
Karsten
CREATE TABLE C_Geodata (
AD_CLIENT_ID NUMBER(10) NOT NULL,
AD_ORG_ID NUMBER(10) NOT NULL,
ISACTIVE CHAR(1 BYTE) DEFAULT 'Y' NOT NULL,
CREATED DATE DEFAULT SYSDATE NOT NULL,
CREATEDBY NUMBER(10) NOT NULL,
UPDATED DATE DEFAULT SYSDATE NOT NULL,
UPDATEDBY NUMBER(10) NOT NULL,
C_Geodata_ID INTEGER NOT NULL,
Name NVARCHAR2(60) NOT NULL,
CountryCode CHAR(2 BYTE) NOT NULL,
Postal VARCHAR2(10 BYTE) NOT NULL,
Postal_Add VARCHAR2(10 BYTE),
Latitude NUMBER,
Longitude NUMBER,
TimeZone NVARCHAR2(60)
);
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi Karsten, all sounds good:
-----------------
1. Store Archive In File System [+1]
-----------------
2. Resort Lines By Pressing Shift+Up/Down [Some questions on tracker]
-----------------
About 3, (geodata.org)
as there is no tracker, I'll ask here:
Can you explain better how it will work.
I don't understand the C_Geodata table. It just has a reference to C_Country, but I think it must have a reference to C_City, and even in some cases maybe we can have a reference directly to C_Location.
Very big cities like Mexico are divided into Delegations and Colonies, and each colony has a different postal code, and is so big that latitude and longitude can vary a lot i.e. within delegations.
I'm not sure how is the best design for this (and I haven't deeply think about it) but possibly a C_Geonames_ID table with corresponding foreign key on city and location can help us.
Is there any GIS expert here to advice on?
Regards,
Carlos Ruiz
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
here is the information you can get from geonames:
The main 'geoname' table has the following fields :
---------------------------------------------------
geonameid : integer id of record in geonames database
name : name of geographical point (utf8) varchar(200)
asciiname : name of geographical point in plain ascii characters, varchar(200)
alternatenames : alternatenames, comma separated varchar(4000)
latitude : latitude in decimal degrees (wgs84)
longitude : longitude in decimal degrees (wgs84)
feature class : see http://www.geonames.org/export/codes.html, char(1)
feature code : see http://www.geonames.org/export/codes.html, varchar(10)
country code : ISO-3166 2-letter country code, 2 characters
cc2 : alternate country codes, comma separated, ISO-3166 2-letter country code, 60 characters
admin1 code : fipscode (subject to change to iso code), isocode for the us and ch, see file admin1Codes.txt for display names of this code; varchar(20)
admin2 code : code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80)
population : integer
elevation : in meters, integer
gtopo30 : average elevation of 30'x30' (ca 900mx900m) area in meters, integer
timezone : the timezone id (see file timeZone.txt)
modification date : date of last modification in yyyy-MM-dd format
Since a city name is not unique you can not generate the reference from geoname entry to an existing C_City directly. So this data is ment more as a reference pool but if you know the country an zip code you can find the right C_Geoname entry and get the information from it. So the import is just to get the data into adempiere as a first step to let others do some usefull stuff with it :)
Regards,
Karsten
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
* I think the table can have all the fields.
* I'm not sure if it must be created as C_Geonames or we must reserve a different prefix for these tables (like GEO_Geoname, GEO_AlternateName, GEO_CountryInfo)
* Are we going to use geonameid as primary key of this table? I mean, this will broke the standard that Value field is for external codes and internal ID must be a sequence.
* Can this enhancement be done initially as a 2pack package instead of trunk?
Future enhancements:
* In future we can add download/refresh/import process for a specific country (or all)
Finally I think this info is not useful if we don't have the GEO_Geoname_ID foreign key in C_City and C_Location.
I understand the first step is simply to create the structures, but we can go beyond.
Regards,
Carlos Ruiz
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
+1 for all three from my side. Features sound good, Google Map, wow!!! that would be wonderful, but what if the client restricts the server running Adempiere from having internet access?
Regards,
Venky
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Just apply the two patches (GridTab and APanel) and have fun :)
Go to an order with some orderlines, switch to grid view and select one line. Now you can resort it by pressing shift+key up/down or shift + icon up/down with the mouse. Please try it with sorted tables also.
Regards,
Karsten
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
DeathMeat is working on such an enhancement - we discussed it on phone. His plan is to store position, width and even existance of the column for each user in an AD table.
Regards,
Karsten
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I think Compiere 2.6.1 have implemented saved user query for tab. Allow user to save query by name or just maintaining the history of query make by user for each tab would be very use full.
Regards,
Low
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
>> His plan is to store position, width and even existance of the column for each user in an AD table.
>
>Very good enhancement!!!
>
>A Tab/User/Preference table can do the trick, to store preference on initial mode (grid/non-grid), order of columns >and sort colum
Such functionality has my positive vote and support too.
Kind regards,
Trifon
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'm not sure about other countries - but I haven't been able to find a correct UK postcode. The postcodes in the project seem to be considerably incomplete in in the 'Outcode' section, and they don't include the 'InCode' at all and hence I sugeest they will never work for the UK. It may be that all postcodes in the UK are owned by the royal mail and licensed. Hopefully, people find this different in other countries.
What would be useful for me is to import the SubDivision codes (http://www.unece.org/cefact/locode/service/sublocat.htm) in to the Regions for each country as they are a complete list for many countries and an already defined open standard (in the UK's case these are also a regional standard - British Standard BS 6879).
Mike
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi,
I did tree enhancements that might be usefull for others too:
1. Store Archive In File System: https://sourceforge.net/tracker/index.php?func=detail&aid=1677797&group_id=176962&atid=879335
The twin of Store Attachments in file system...
2. Resort Lines By Pressing Shift+Up/Down: https://sourceforge.net/tracker/index.php?func=detail&aid=1672362&group_id=176962&atid=879335
3. Johannes (DeatMeat) had the idea and the source (geodata.org)... Add geodata to ADempiere. I did a table and the import process to fill it with data from geonames.org (http://download.geonames.org/export/dump/). They could be used for things like - autofill the city name for entered zip code (or the other way round) - generate a google map with all your customers - use it for reporting ...
So what do you think?
Regards,
Karsten
CREATE TABLE C_Geodata (
AD_CLIENT_ID NUMBER(10) NOT NULL,
AD_ORG_ID NUMBER(10) NOT NULL,
ISACTIVE CHAR(1 BYTE) DEFAULT 'Y' NOT NULL,
CREATED DATE DEFAULT SYSDATE NOT NULL,
CREATEDBY NUMBER(10) NOT NULL,
UPDATED DATE DEFAULT SYSDATE NOT NULL,
UPDATEDBY NUMBER(10) NOT NULL,
C_Geodata_ID INTEGER NOT NULL,
Name NVARCHAR2(60) NOT NULL,
CountryCode CHAR(2 BYTE) NOT NULL,
Postal VARCHAR2(10 BYTE) NOT NULL,
Postal_Add VARCHAR2(10 BYTE),
Latitude NUMBER,
Longitude NUMBER,
TimeZone NVARCHAR2(60)
);
Hi Karsten,
Very cool!
1. +1
2. +1
3. +1
Joel Stangeland
Hi Karsten,
It is wild time...
My vote is [+1] for all three features.
Kind regards,
Trifon
Hi Karsten!
All those features rocks!
My vote to include them!
Regards!
yes, 3 [+1]s from me too!
colin
thank you guys thats all I wanted to know :)
I'll put them into trunk until end of the week.
Regards,
Karsten
Hi Karsten, all sounds good:
-----------------
1. Store Archive In File System [+1]
-----------------
2. Resort Lines By Pressing Shift+Up/Down [Some questions on tracker]
-----------------
About 3, (geodata.org)
as there is no tracker, I'll ask here:
Can you explain better how it will work.
I don't understand the C_Geodata table. It just has a reference to C_Country, but I think it must have a reference to C_City, and even in some cases maybe we can have a reference directly to C_Location.
Very big cities like Mexico are divided into Delegations and Colonies, and each colony has a different postal code, and is so big that latitude and longitude can vary a lot i.e. within delegations.
I'm not sure how is the best design for this (and I haven't deeply think about it) but possibly a C_Geonames_ID table with corresponding foreign key on city and location can help us.
Is there any GIS expert here to advice on?
Regards,
Carlos Ruiz
Hi Carlos,
here is the information you can get from geonames:
The main 'geoname' table has the following fields :
---------------------------------------------------
geonameid : integer id of record in geonames database
name : name of geographical point (utf8) varchar(200)
asciiname : name of geographical point in plain ascii characters, varchar(200)
alternatenames : alternatenames, comma separated varchar(4000)
latitude : latitude in decimal degrees (wgs84)
longitude : longitude in decimal degrees (wgs84)
feature class : see http://www.geonames.org/export/codes.html, char(1)
feature code : see http://www.geonames.org/export/codes.html, varchar(10)
country code : ISO-3166 2-letter country code, 2 characters
cc2 : alternate country codes, comma separated, ISO-3166 2-letter country code, 60 characters
admin1 code : fipscode (subject to change to iso code), isocode for the us and ch, see file admin1Codes.txt for display names of this code; varchar(20)
admin2 code : code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80)
population : integer
elevation : in meters, integer
gtopo30 : average elevation of 30'x30' (ca 900mx900m) area in meters, integer
timezone : the timezone id (see file timeZone.txt)
modification date : date of last modification in yyyy-MM-dd format
Since a city name is not unique you can not generate the reference from geoname entry to an existing C_City directly. So this data is ment more as a reference pool but if you know the country an zip code you can find the right C_Geoname entry and get the information from it. So the import is just to get the data into adempiere as a first step to let others do some usefull stuff with it :)
Regards,
Karsten
Hi Karsten, understood.
There are instructions on creating such tables on postgres here:
http://forum.geonames.org/gforum/posts/list/67.page
* I think the table can have all the fields.
* I'm not sure if it must be created as C_Geonames or we must reserve a different prefix for these tables (like GEO_Geoname, GEO_AlternateName, GEO_CountryInfo)
* Are we going to use geonameid as primary key of this table? I mean, this will broke the standard that Value field is for external codes and internal ID must be a sequence.
* Can this enhancement be done initially as a 2pack package instead of trunk?
Future enhancements:
* In future we can add download/refresh/import process for a specific country (or all)
Finally I think this info is not useful if we don't have the GEO_Geoname_ID foreign key in C_City and C_Location.
I understand the first step is simply to create the structures, but we can go beyond.
Regards,
Carlos Ruiz
1) Yes, it be good 2learn2 how2 use 2pack.
2) About GIS expert, i wonder if a Spatial Information project involving Punkish here who had coffee with Ramiro in Chile last November may be the one -
https://sourceforge.net/forum/forum.php?thread_id=1610399&forum_id=611158
I m terribly excited about (3) Go GoogleMap!
red1
+1 for all three from my side. Features sound good, Google Map, wow!!! that would be wonderful, but what if the client restricts the server running Adempiere from having internet access?
Regards,
Venky
Hi Karsten,
Very good enhancements!
[+1] for all three features
Regards,
Alejandro
Hi,
first one (line sort) is online for testing: http://sourceforge.net/tracker/index.php?func=detail&aid=1672362&group_id=176962&atid=879335
Just apply the two patches (GridTab and APanel) and have fun :)
Go to an order with some orderlines, switch to grid view and select one line. Now you can resort it by pressing shift+key up/down or shift + icon up/down with the mouse. Please try it with sorted tables also.
Regards,
Karsten
Hi,
store archive on file system is in trunk with rev. 2474/2475
http://sourceforge.net/tracker/index.php?func=detail&aid=1677797&group_id=176962&atid=879335
Regards,
Karsten
Hi Karsten,
I will try it ASAP and let you know. Otherwise -if I run out of time- we will talk in Berlin.
As you seem to be fit regarding grid handling, sometimes I wish that the column order in the grid should be the same after I have change it.
Best regards
Mario Calderon
Hi,
DeathMeat is working on such an enhancement - we discussed it on phone. His plan is to store position, width and even existance of the column for each user in an AD table.
Regards,
Karsten
> His plan is to store position, width and even existance of the column for each user in an AD table.
Very good enhancement!!!
A Tab/User/Preference table can do the trick, to store preference on initial mode (grid/non-grid), order of columns and sort colum.
Not sure if we can extend such table to store also useful queries by user.
Regards,
Carlos Ruiz
I think Compiere 2.6.1 have implemented saved user query for tab. Allow user to save query by name or just maintaining the history of query make by user for each tab would be very use full.
Regards,
Low
Hi team,
>> His plan is to store position, width and even existance of the column for each user in an AD table.
>
>Very good enhancement!!!
>
>A Tab/User/Preference table can do the trick, to store preference on initial mode (grid/non-grid), order of columns >and sort colum
Such functionality has my positive vote and support too.
Kind regards,
Trifon
It may well be the same data - but these items are an open standard http://www.unece.org/cefact/locode/
I'm not sure about other countries - but I haven't been able to find a correct UK postcode. The postcodes in the project seem to be considerably incomplete in in the 'Outcode' section, and they don't include the 'InCode' at all and hence I sugeest they will never work for the UK. It may be that all postcodes in the UK are owned by the royal mail and licensed. Hopefully, people find this different in other countries.
What would be useful for me is to import the SubDivision codes (http://www.unece.org/cefact/locode/service/sublocat.htm) in to the Regions for each country as they are a complete list for many countries and an already defined open standard (in the UK's case these are also a regional standard - British Standard BS 6879).
Mike
PS - my post is about the geonames info only .....
Too brillant.
How is the map now?
+1 for all