|
|
|
Nearest to Postcode Address Functionality:
|
Advantages/Uses:
-
Nearest store
functionality
-
Nearest consultants,
agents, garages, office, houses for sale
-
Use Local Data
held in your Database,
for maximum flexibility
-
Use Web Data, for
easy integration. Upload of data via Microsoft Excel
-
Use Web Data, for
displaying Google Map with pins
-
Earn commission by
becoming a reseller
[More About being a Reseller]
|
|
The following discusses implementing the
nearest store functionality, but the Stores could be consultants,
agents, garages, offices, companies, houses for sale, etc….
We offer two methods of calculating the Nearest
to Postcode:
-
Option
1 Local Data, best
for large number of addresses which are dynamic
This option uses your
table of Stores, and our COM object to retrieve the Nearest
data. Only one call is made to our server during this
operation, or none if the COM Object is in Local
Mode.
Demo
Video : Show me Option 1 in action and how to do nearest
The principle is:
[Click
here for more information on COM Object]
You have two indexed fields in your “Store” database table
called Longitude and Latitude.
You write a procedure to call our PostZon
database to populate the Longitude and Latitude fields, for any
where Longitude and Latitude = 0. You then
either call this after bulk additions, or populate these fields
every time new records are added.
[Click
here for code]
You provide the user with some kind of query window/web page, to
enter their Postcode. When
queried you then call the Nearest_StartList(TargetPostcode, WithInKm,
Longitude, Latitude, Difference) data call to get the Difference,
Longitude and Latitude for the query Postcode.
Then
query your database with SQL:
"((Nearest.Longitude)>" &
Longitude - Difference
& ") and ((Nearest.Longitude)<" &
Longitude + Difference
& ") and "
"((Nearest.Latitude)>" & Latitude - Difference
& ") and ((Nearest.Latitude)<" & Latitude +
Difference
& ")"
For
each result you then call Nearest_AddToList(RecordLongitude,
RecordLatitude, YouData1, YouData2, YouData3)
Where YouData1, YouData2, YouData3
can be any string data you want to display or use to identify
records.
The
COM Object now has a List of results.
So
call Nearest_GetNumberOfRecordsReturned()
to Get Number of records found and sort them
Then
to retrieve the data
For x = 1 To NumberOfRecordToDisplay
.Nearest_GetRecord x, Distance, Longitude, Latitude, YouData1,
_
YouData2, YouData3
'Build string to display
GetAddressesWithIn = GetAddressesWithIn & Distance & " " &
_
YourData1 & " " & YouData2 & vbCrLf
Next
Further
pagination functionality can then be added to this process.
If
the process returns a very small number of results, then widen the
search area, if need be.
We
have a Microsoft Access Example which demonstrates this
functionality, contained in our Programming Examples Download. see
"Using COM Object.mdb". This contains a table called
"Nearest" which contains 500 example address.
Please download examples here
How to populate your table with
Longitude and Latitude fields:
Dim Rec As New ADODB.Recordset
Dim SQL As String, DataKey As String, c&
SQL = "SELECT Nearest.* FROM Nearest WHERE (((Nearest.Longtitude)=0) and ((Nearest.Latitude)=0));"
'Open connection for updating records
Rec.Open SQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic ', adCmdTableDirect"
'Get Data Key for accessing the Postcode data
DataKey = NullToBlank(Forms![mainForm].txtDataKey)
If Not Rec.BOF Then Rec.MoveFirst
Do While Not Rec.EOF
Me.Caption = "Filling in Postcode:" & Rec![Postcode]
Dim LongValue#, LatValue#, KeyValue#
If GetLongLat(DataKey, Rec![Postcode], LongValue, LatValue) Then
'Go Longtitude/Latitude , so put in record
Rec![Longtitude] = LongValue
Rec![Latitude] = LatValue
Rec.Update
End If
'Next record
Rec.MoveNext
c = c + 1
Loop
Me.Caption = "Updated " & c & " records, Finished"
Rec.Close
And Get Longitude and Latitude from our
Database:
Function GetLongLat(DataKey$, Postcode$, RetLong#, RetLat#) As Boolean
'Get Longitude/Latitude from PostZon database
SimplyPostCodeLookup.SetDataKey (DataKey)
With SimplyPostCodeLookup
If .GetPostZonAddressRecord(Postcode) Then
'Add data to records
RetLong = .PostZon_Longitude_wgs84
RetLat = .PostZon_Latitude_wgs84
GetLongLat = True
End If
End With
End Function
How to display 10 nearest addresses to
Target Postcode:
Answer = GetAddressesWithIn(DataKey, TargetPostcode, WithInKm,
_
MaxRecords, NumberOfRecordsReturned)
MsgBox "Found:" & NumberOfRecordsReturned & vbCrLf & Answer
And the function:
Function GetAddressesWithIn(DataKey$, TargetPostcode$, WithInKm#,
_
MaxNumber, NumberOfRecordsReturned&) As String
'Get addresses within xKm of target Longitude/Latitude
Dim Rec As New ADODB.Recordset
Dim SQL As String, c&, Difference#
Dim YouData1$, YouData2$, YouData3$, x&, Distance#, Longitude#, Latitude#
With SimplyPostCodeLookup
.SetDataKey (DataKey)
'Get Differance in Longitude/Latitude from COM Object
'then we can get all addresses within +- x km of target
If .Nearest_StartList(TargetPostcode, WithInKm#, Longitude,
_
Latitude, Difference) Then
SQL = "SELECT Nearest.* FROM Nearest WHERE ("
SQL = SQL & "((Nearest.Longitude)>" & Longitude - Difference &
_
") and ((Nearest.Longitude)<" & Longitude + Difference & ") and "
SQL = SQL & "((Nearest.Latitude)>" & Latitude - Difference &
_
") and ((Nearest.Latitude)<" & Latitude + Difference & ")"
SQL = SQL & ");"
'Open connection of records
Rec.Open SQL, CurrentProject.Connection, adOpenForwardOnly
NumberOfRecordsReturned = 0
If Not Rec.BOF Then Rec.MoveFirst
Do While Not Rec.EOF
'Now give the COM object the record details.
'To sort into Nearest order later
'DO NOT put "|" in your data
YouData1 = Rec![Postcode] 'This could be any data you want
YouData2 = Rec![Address]
.Nearest_AddToList
Rec![Longitude], Rec![Latitude], _
YouData1, YouData2, YouData3
'Next record
Rec.MoveNext
c = c + 1
Loop
'Now get the number of records returned withinKm
'The COM Object now sorts the results in memory
NumberOfRecordsReturned = .Nearest_GetNumberOfRecordsReturned()
Dim NumberOfRecordToDisplay&
NumberOfRecordToDisplay = NumberOfRecordsReturned
If NumberOfRecordToDisplay > MaxNumber Then
_
NumberOfRecordToDisplay = MaxNumber
'Now get list for displaying
For x = 1 To NumberOfRecordToDisplay
.Nearest_GetRecord x, Distance, Longitude, Latitude, YouData1,
_
YouData2, YouData3
GetAddressesWithIn = GetAddressesWithIn & Distance & " " &
_
YourData1 & " " & YouData2 & vbCrLf
Next
'now inform COM Object we are done so it can clear the memory
.Nearest_EndList
Else
MsgBox "Error:" & .General_errormessage & " calling
Nearest_StartList"
End If
End With
End Function
This functionality can be implemented in PHP using one of the many
"Nearest Store" PHP examples available on the
internet. The example in the "PHP Core Code\PostZon core code"
shows how to get the Longitude and Latitude in from our service in
PHP.
-
Option
2 Web Data, best for a small to medium, relatively static list
of addresses
Using
this option you load the list of stores into our database, and
then make a simple request for nearest Stores to a given
Postcode. The call returns your Store data, merged with
your template, to format the output into HTML which can be
directly written to a web page. You have
total control over the appearance of your page.
A more advance call
is available to return a Google map with map pins, which when
clicked on can display data from your templates in up to four
tabs. The pins can also have different Icons to allow you
to display other points of interest, other than your stores.
The Map or Nearest
list can be retrieved by an SOAP or HTTP call
The example downloads
include a PHP and .NET 2005 web page to return a simple page with map
and nearest list.
Tutorial
Video : Show me Option 2
(Sound
Required)
Option 2:
Nearest Requirements
This
functionality requires an Internal Use PostZon License, to allow
data to be uploaded, and used internally on one machine. To
trial the system you can simply used LicenseID =
0, this will give 500
demo address in list 1 and 2 as listed on the upload spreadsheet.
[Prices]
[Click
here for live Web Demo of Option 2]
Note:
Any lists or templates that has not been used for 6 months will
deleted.
Nearest Web Data
We offer a Web based solution to allow the
location of Stores to be loaded onto our server, either by putting
the data in an Excel spreadsheet and simply importing the data using the tool
provided by us. Or for those who want total integration we
provide a Web SOAP Service which allows calls to manage list by your program/web site.
Then a simple call web page, SOAP service or HTTP request to
return the closest stores to the TargetPostCode.
[Click here to see simple web page results]
The returned results can be either returned as
XML or merged with
a template you provide to give desired HTML to produce the
list. So simply call using server side script, and pop
the returned HTML into your page . Another function will
return Google Map code, together with the HTML to display results on
a map together with the nearest store list.
If the process returns a very small number of
results, then widen the search area if need be.
[Download
Pins used in examples]
Our
"Store" database structure is:
|
Name
|
Description
|
|
YourID
|
Your identifier, you can use to get full
record from your database.
UNIQUE
REQUIRED Integer
|
|
YourData1
|
This
can be loaded with any text you want to display. This could be
loaded with the ID of your record, full address, directions,
etc.
This
data is then merged with your template to expand to full HTML
[Click
here for simple example]
|
|
YourData2
|
As YourData1
|
|
YourData3
|
As YourData1
|
|
YourData4
|
As YourData1
|
|
YourData5
|
As YourData1
|
|
YourData6
|
As YourData1
If using filters
then can stop details being shown on pin click if set to "NODETAILS".
This allows control over each Pin
|
|
YourData7
|
As YourData1
|
|
YourData8
|
As YourData1
|
|
Longitude
|
We populate this for you when added to
database
|
|
Latitude
|
We populate this for you when added to
database
|
|
PostCode
|
The Postcode of your store.
REQUIRED
|
|
DataAdded
|
Date
added to database.
We
assign this when added.
format
YYYYMMDD
|
|
Type
|
Can
be used to filter out certain kinds of locations, in nearest
list and show different icons on a map
Optional
Char
[See
Example 5]
TypeFilter
will optionally filter out records from the nearest
list. You could use "A" for major stores,
"B" small stores and "C" for Petrol
stations. Then if displayed on map they could all have
different Pins. The actual list returned could
only include "AB", so the petrol stations would not
be listed.
This field could also be loaded with codes, which
relate to qualifications. Then the service is called with
the type code (qualification code) to product a list of
nearest consultants with curtail qualifications. |
|
Area
|
Allows you to
display all Stores in a certain area.
Optional String (2)
To display a map
for Area simple call the
GetNearestToPostCodeTemplateAndMap
with
ExtraFlags="AreaCode=Area"
the TargetPostcode should be set to center the
map
[See
Example 6,7]
|
The Excel spreadsheet
allows you to upload the above data very easily
Nearest to Postcode Functions Provided are:
Common Parameters:
|
LicenseID
|
Your License ID (Account Number)
This is given to you
when you open an account with us
|
|
ListID
|
1-10, allows you to have multiple lists.
|
|
Password
|
Password to your account |
|
ErrorDesc
|
Returns any errors if they occur, other wise
“” if OK |
For Trail use set
to 0. ListID=1 and 2 which are List 1 and 2 on the
spreadsheet. When calling these lists the DataKey,
LicenseID and Password
are ignored. You will NOT be able to update
these list.
SOAP Functions to Manage nearest Stores List:
The
following functions allow you to manage your store list on our
server, but we provide an Excel spreadsheet which allows you to
easily manage the list from with Microsoft Excel.
[Download Data
Importer]
SOAP
Service URL:
http://www.simplylookupadmin.co.uk/webservicenearest.asmx
RemoveAllFromList(LicenseID
as long,_
ListID as long,_
Password as string,_
ByRef ErrorDesc as string)
as Boolean
Returns
TRUE if removed, else error in ErrorDesc
if
ListID =-1 then delete all lists
RemoveEntryFromList(LicenseID
as long,_
ListID as long,_
Password as string,_
ByRef ErrorDesc as string,_
YourID as Long)
as Boolean
Returns
TRUE if removed, else error in ErrorDesc
GetListCount(LicenseID
as long,_
ListID as long,_
Password as string,_
ByRef ErrorDesc as string,_
YourCurrentLimit as long)
as long
Returns
count, else –1 and error in ErrorDesc
YourCurrentLimit is the list limit you currently have. if
ListID =-1 then get count for all lists
AddOrUpdateEntry(LicenseID
as long,_
Datakey as string,_
ListID as long,_
Password as string,_
ByRef ErrorDesc as string,_
MachineID as string,_
Postcode as string,_
YourID as long, _
YourData1 as string,_
YourData2 as string,_
YourData3 as string,_
YourData4 as string,_
YourData5 as string,_
YourData6 as string,_
YourData7 as string,_
YourData8 as string,_
Type as string,_
Area as string) as Boolean
Returns
TRUE if added/updated, else error in ErrorDesc
MachineID
must be the Computer name of the machine calling the function
Datakey
is you datakey given to you when you opened the account
GetAllInList(LicenseID
as long,_
ListID as long,_
Password as string,_
ByRef ErrorDesc as string,_
ExtraParameters as string)
as String
Returns
XML List if set, else error in ErrorDesc
ExtraParameters
may be used to filter the results in future versions
Returns:
<list>
<errordesc></errordesc>
<recordcount>2</recordcount>
<record>
<yourid>531252</yourid>
<yourdata1>My store at Wisbech</yourdata1>
<yourdata2>Turn left at the polices station. <b>Phone
08512 251258</b></yourdata2>
<yourdata3>ShowDetails.aspx?=829835</yourdata3>
<yourdata4></yourdata4>
<yourdata5></yourdata5>
<yourdata6></yourdata6>
<yourdata7></yourdata7>
<yourdata8></yourdata8>
<type></type>
<area></area>
<postcode>PE132XQ</Postcode>
<longitude>2.3512126</longitude>
<latitude>1.2162363</latitude>
<dateadded>20070304</dateadded>
</record>
<record>
<yourid>531253</yourid>
<yourdata1>My store at Kings Lynn</yourdata1>
<yourdata2>Turn right at the polices station. <b>Phone
08512 253522</b></yourdata2>
<yourdata3>ShowDetails.aspx?=829833</yourdata3>
<yourdata4></yourdata4>
<yourdata5></yourdata5>
<yourdata6></yourdata6>
<yourdata7></yourdata7>
<yourdata8></yourdata8>
<type></type>
<area></area>
<postcode>PE132XQ</postcode>
<longitude>2.3542126</longitude>
<latitude>1.2122233</latitude>
<dateadded>20070304</dateadded>
</record>
</list>
GetEntryFromList(LicenseID
as long,_
ListID as long,_
Password as string,_
ByRef ErrorDesc as string,_
YourID as long)
as String
Returns
XML List if set, else error in ErrorDesc
SOAP Functions to Manage Nearest Templates:
The
Templates drive the system, please look at the web demo page and
tick "show template information" to examine how the system
works. [Web
Demo Page] [Template
example]
SOAP
Service URL:
http://www.simplylookupadmin.co.uk/webservicenearest.asmx
SetTempate(LicenseID
as long,_
TemplateID as long,_
Password as string,_
ByRef ErrorDesc as string,_
HeaderTemplate as string,_
DetailsTemplate as string,_
FooterTemplate as string,_
NextPageURL as string) as Boolean
SetTempateWithGoogleDetails(LicenseID
as long,_
TemplateID as long,_
Password as string,_
ByRef ErrorDesc as string,_
HeaderTemplate as string,_
DetailsTemplate as string,_
FooterTemplate as string,_
NextPageURL as string,_
MapIconsLocation as string,_
ShowTargetPin as boolean,_
ShowShadowOnPins as boolean,_
UseNumberedPins as boolean,_
UseTypeFilterPins as boolean,_
MapTargetPin_Details as string,_
Target_PinIcon_Details as string,_
Included_PinIcon_Details as string,_
FilteredOut_PinIcon_Details as string,_
MapTabTemplate1 as string,_
MapTabTemplate2 as string,_
MapTabTemplate3 as string,_
MapTabTemplate4 as
string,_
ExtraFlags as string) as Boolean
Returns
TRUE if set template for List, else error in ErrorDesc
The HeaderTemplate,DetailsTemplate
and FooterTemplate
can be any text which contains a series of tags, which will
be replaced by the data in your table
Header Tags for use in Templates
For
DetailsTemplate:
| Tag |
Description |
|
_yourid_
|
Replaced with Your ID,from the database |
|
_yourdata1_
|
Replaced with Your Data1, from the database |
|
_yourdata2_
|
Replaced with Your Data2, from the database |
|
_yourdata3_
|
Replaced with Your Data3, from the database |
|
_yourdata4_
|
Replaced with Your Data4, from the database |
|
_yourdata5_
|
Replaced with Your Data5, from the database |
|
_yourdata6_
|
Replaced with Your Data6, from the database
If using filters
then can stop details being shown on pin if "NODETAILS"
|
|
_yourdata7_
|
Replaced with Your Data7, from the database |
|
_yourdata8_
|
Replaced with Your Data8, from the database |
|
_postcode_
|
Replaced with postcode,from the database |
|
_longitude_
|
Replaced with Longitude,from the database |
|
_latitude_
|
Replaced with Latitude,from the database |
|
_km_ or _mile_
|
Distance from target |
|
_dateadded_
|
Replaced with date added to the database |
|
_rank_
|
Replaced with rank count for page of results |
|
_type_
|
Replaced with type |
|
_alphcount_
|
Replaced with A,B,C... count for page of results |
For HeaderTemplate
and FooterTemplate:
| Tag |
Description |
|
_recordcount_
|
Replaced with record count of results
found in list. |
|
_pincount_
|
Replaced with map pin count of results
found in list. |
|
_listcount_
|
Replaced with number of items in
list on current page |
|
_nopages_
|
Replaced with number of pages of results |
|
_pageno_
|
Replaced with page number |
|
_pageselection_
|
Replaced with link to pages [1] [2]
[3]... |
|
_previouspage_
|
Replaced with link to previous page
if on page 2 onwards |
|
_nextpage_
|
Replaced with link to next page if
on page 1 to last but one. See example below... |
For NextPageURL:
| Tag |
Description |
|
_targetpostcode_
|
Replaced with Target Postcode in
link |
|
_page_
|
Replaced with
Target Page in link
See example
below... |
All must be in lower case
All the other parameters are used by mapping
functions, and can be left blank if map is not needed.
IconsLocation
is optional, but should point to web directory where pin icons can
be found. Icons should be named "icon" &
type & ".png" so different icons can be used for
different types of nearest property. Such as schools etc. If
blank will use default Google pin. If type is not used you
should give a "icon.png", and "shadicon.png" if
shadows are on. [See
Example 5 shows type icons]
if
ShowTargetPin
then displays a ping for the Target Postcode "icontarget.png",
and "shadicontarget.png" if shadows are on.
[See
Example 2 and 3]
TargetPin_Details
show if user clicks on pins. Leave blank if not needed.
Click on target pin in example 2 and 3.
ShowShadowOnPins,
if true then uses shadow pins. Uses the same names as other
icon with prefix "shad". Therefore "icontarget.png"
becomes "shadicontarget.png". If using filter
on types then icon for shadow is called "shadiconfilterout.png".
All examples use shadows.
UseNumberedPins
if True then uses "iconx.png" where x is the rank
in the results. This allows you to create pins representing
1-10 (or A-Z) and display them which match the text results (using
_rank_ or
_alphcount_ tag to match). [See
Example 4 and 5]
Target_PinIcon_Details,Included_PinIcon_Details
and
FilteredOut_PinIcon_Details
should
be a list of co-ordinates for each type of icon. IconsSizeX,
IconSizeY, ShadowSizeX, ShadowSizeY, IconAnchorX, IconAnchorY,
InfoWindowAnchorX and InfoWindowAnchorY
TabTemplate1
template to merge with your data to show details when user clicks on
pin. Leave blank to just show pins. [See
Example 2]
TabTemplate2-4
extra tabs on pins. | | | |
Log
In
