-
Notifications
You must be signed in to change notification settings - Fork 2
gisgraphy
- We can directly query postgressql (after knowing database and its structure) to fetch location and its attributes.This will helpful when we query directly from database
- For curl or http request we use Gisgraphy webservice url or Solr Apache. Only difference in Solr we only access location details using location name.
- We can Push data both in Postgres sql and Solr. Currently we have code in PHP to push the data
- We can Pop data from postgres .Currentl we have code in both PHP and Java to pull Location.
There is a two basic requirement of gisgraphy:
- Junction (Chauraha)
- Road (full road and its length)
Type the following command on the Terminal (Ubuntu Linux)
sudo -i -u postgres
psql -d postgres
\connect gisgraphy
Exit out of the PostgreSQL prompt by typing:\q
\list or \l: list all databases
\dt: list all tables in the current database
to describe table : \d+ road
For more information:https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-14-04
Database Table:Road
Structure:
id (bigint not null)
name ( character varying(200) not null)
location (geometry not null )
source (character varying(255) not null)
timezone ( character varying(40))
countrycode (character varying(3))
adm1code ( character varying(20))
adm2code (character varying(80))
adm3code ( character varying(20))
adm4code ( character varying(20))
featureid ( bigint not null)
modificationdate (timestamp without time zone)
adm1name (character varying(200))
adm2name (character varying(200))
adm3name (character varying(200))
adm4name (character varying(200))
asciiname (character varying(200))
elevation (integer)
gtopo30 (integer)
population (integer)
featureclass (character varying(4))
featurecode (character varying(10))
adm (bigint)
Indexes:
"road_pkey" PRIMARY KEY, btree (id)
"road_featureid_key" UNIQUE CONSTRAINT, btree (featureid)
"roadadm1codeindex" btree (adm1code)
"roadgisfeaturecountryindex" btree (countrycode)
"roadgisfeatureidindex" btree (featureid)
Foreign-key constraints:
"fk637222053aef83d26f340" FOREIGN KEY (adm) REFERENCES adm(id)
Tables Column Defination:
location (geometry not null): Location must be in long,lat in geom format with Spatial Reference **SRID=4326 **
-
A Spatial Reference System Identifier (SRID) is a unique value used to unambiguously identify projected, unprojected, and local spatial coordinate system definitions. These coordinate systems form the heart of all GIS applications.
For more please see table : TABLE SPATIAL_REF_SYS
and follow:https://en.wikipedia.org/wiki/SRID _
In PHP to insert in location column:'SRID=4326;POINT('.$lng_new.' '.$lat_new.')'_ -
id (bigint not null) and featureid ( bigint not null)
Before push road ,first get max id and max feature_id from table road ,adm,politicalentity,gisfeature,country,religious,waterbody,bay,ice,stream, and many more table. See later code for for getting max id and feature_id
**It must be because there is no auto increment and the same indexing of feature id will also in Solr. If mismatch then by gisgraphy webservice will not return correct data or it may not return **
Code in PHP:
$max_fid1=array();
$max_id1=array();
$result2 = pg_query($db_connection, "select max(r.featureid),max(r.id) from road as r union select max(a.featureid),max(a.id) from adm as a union select max(p.featureid),max(p.id) from politicalentity as p union select max( g.featureid),max(g.id) from gisfeature as g union Select max(c.featureid),max(c.id) from country as c union Select max(rg.featureid),max(rg.id) from religious as rg union Select max(w.featureid),max(w.id) from waterbody as w union Select max(b.featureid),max(b.id) from bay as b union Select max(i.featureid),max(i.id) from ice as i union Select max(s.featureid),max(s.id) from stream as s union Select max(aq.featureid),max(aq.id) from aqueduc as aq union Select max(fh.featureid),max(fh.id) from fishingarea as fh union Select max(fj.featureid),max(fj.id) from fjord as fj union Select max(fl.featureid),max(fl.id) from falls as fl union Select max(gu.featureid),max(gu.id) from gulf as gu union Select max(po.featureid),max(po.id) from port as po union Select max(lk.featureid),max(lk.id) from lake as lk union Select max(pnd.featureid),max(pnd.id) from pond as pnd union Select max(ocn.featureid),max(ocn.id) from ocean as ocn union Select max(ref.featureid),max(ref.id) from reef as ref union Select max(se.featureid),max(se.id) from sea as se union Select max(sp.featureid),max(sp.id) from spring as sp union Select max(st.featureid),max(st.id) from strait as st union Select max(ms.featureid),max(ms.id) from marsh as ms union Select max(tnl.featureid),max(tnl.id) from tunnel as tnl union Select max(ap.featureid),max(ap.id) from amusepark as ap union Select max(mit.featureid),max(mit.id) from military as mit union Select max(prk.featureid),max(prk.id) from park as prk union Select max(ctn.featureid),max(ctn.id) from continent as ctn union Select max(fld.featureid),max(fld.id) from field as fld union Select max(mne.featureid),max(mne.id) from mine as mne union Select max(os.featureid),max(os.id) from oasis as os union Select max(rs.featureid),max(rs.id) from reserve as rs union Select max(ft.featureid),max(ft.id) from forest as ft union Select max(ct.featureid),max(ct.id) from city as ct union Select max(ctsd.featureid),max(ctsd.id) from citysubdivision as ctsd union Select max(rl.featureid),max(rl.id) from rail as rl union Select max(bl.featureid),max(bl.id) from building as bl union Select max(ar.featureid),max(ar.id) from airport as ar union Select max(tr.featureid),max(tr.id) from theater as tr union Select max(std.featureid),max(std.id) from stadium as std union Select max(atms.featureid),max(atms.id) from atm as atms union Select max(bnk.featureid),max(bnk.id) from bank as bnk union Select max(brg.featureid),max(brg.id) from bridge as brg union Select max(cem.featureid),max(cem.id) from cemetery as cem union Select max(bs.featureid),max(bs.id) from busstation as bs union Select max(vi.featureid),max(vi.id) from vineyard as vi union Select max(cp.featureid),max(cp.id) from camp as cp union Select max(cs.featureid),max(cs.id) from casino as cs union Select max(cst.featureid),max(cst.id) from castle as cst union Select max(cus.featureid),max(cus.id) from customspost as cus union Select max(ch.featureid),max(ch.id) from courthouse as ch union Select max(ho.featureid),max(ho.id) from hospital as ho union Select max(dm.featureid),max(dm.id) from dam as dm union Select max(plt.featureid),max(plt.id) from plantation as plt union Select max(frm.featureid),max(frm.id) from farm as frm union Select max(grd.featureid),max(grd.id) from garden as grd union Select max(hs.featureid),max(hs.id) from house as hs union Select max(ht.featureid),max(ht.id) from hotel as ht union Select max(qy.featureid),max(qy.id) from quay as qy union Select max(lib.featureid),max(lib.id) from library as lib union Select max(lh.featureid),max(lh.id) from lighthouse as lh union Select max(ml.featureid),max(ml.id) from mall as ml union Select max(fc.featureid),max(fc.id) from factory as fc union Select max(mil.featureid),max(mil.id) from mill as mil union Select max(mn.featureid),max(mn.id) from monument as mn union Select max(mol.featureid),max(mol.id) from mole as mol union Select max(met.featureid),max(met.id) from metrostation as met union Select max(mus.featureid),max(mus.id) from museum as mus union Select max(obs.featureid),max(obs.id) from observatorypoint as obs union Select max(opera.featureid),max(opera.id) from operahouse as opera union Select max(pk.featureid),max(pk.id) from parking as pk union Select max(po.featureid),max(po.id) from postoffice as po union Select max(pp.featureid),max(pp.id) from policepost as pp union Select max(pr.featureid),max(pr.id) from prison as pr union Select max(py.featureid),max(py.id) from pyramid as py union Select max(glf.featureid),max(glf.id) from golf as glf union Select max(rc.featureid),max(rc.id) from ranch as rc union Select max(rldt.featureid),max(rldt.id) from railroadstation as rldt union Select max(sch.featureid),max(sch.id) from school as sch union Select max(tow.featureid),max(tow.id) from tower as tow union Select max(zo.featureid),max(zo.id) from zoo as zo union Select max(lke.featureid),max(lke.id) from lake as lke union Select max(br.featureid),max(br.id) from bar as br union Select max(bh.featureid),max(bh.id) from beach as bh union Select max(clf.featureid),max(clf.id) from cliff as clf union Select max(cny.featureid),max(cny.id) from canyon as cny union Select max(crq.featureid),max(crq.id) from cirque as crq union Select max(dsrt.featureid),max(dsrt.id) from desert as dsrt union Select max(grg.featureid),max(grg.id) from gorge as grg union Select max(hl.featureid),max(hl.id) from hill as hl union Select max(il.featureid),max(il.id) from island as il union Select max(md.featureid),max(md.id) from mound as md union Select max(mnt.featureid),max(mnt.id) from mountain as mnt union Select max(vlc.featureid),max(vlc.id) from volcano as vlc union Select max(usea.featureid),max(usea.id) from undersea as usea union Select max(tre.featureid),max(tre.id) from tree as tre");
while($row2 = pg_fetch_row($result2))
{
$max_fid1[]=$row2[0];
$max_id1[]=$row2[1];
}
$maxfid=max($max_fid1);
$maxid=max($max_id1);
Featureclass and FeatureCode:
These Column is optional but better to fill.
Example Featureclass => 'R'
FeatureCode => 'RDJCT'
For more information see gisgraphy feature code list:_ http://www.geonames.org/export/codes.html_
//============paramters for inserting
$param_junction=array();
$solr_junction_param=array();
$countryCode="IN";
$source="PERSONAL";
$lat_new="26.479975014372428";
$lng_new="80.30555248260498";
$location_base='SRID=4326;POINT('.$lng_new.' '.$lat_new.')';
$location_name="Gol Churaha, Kanpur";
$feature_class="R";
$feature_code="RDJCT"; //junction
$placetype='ROAD';
$country_name="India";
$modificationdate=$date;
$param_junction[]=array('countryCode'=>$countryCode,'source'=>$source,'location'=>$location_base,'name'=>$location_name,'featurecode'=>$feature_code,'featureclass'=>$feature_class,'modificationdate'=>$modificationdate,'lat'=>$lat_new,'lng'=>$lng_new,'country_name'=>$country_name,'placetype'=>$placetype);
$query_string="insert into Road (countryCode, featureid, featureclass, featurecode, location, name, modificationdate, source, id) values ('$countryCode', $new_max_fid, '$featureclass', '$featurecode', '$location', '$name', '$modificationdate', '$source', $new_max_id)";
$result_insert=pg_query($db_connection,$query_string);
After Success put an array for making document to push in Solr Apache
$solr_junction_param[]=array('country_code'=>$countryCode,'country_flag_url'=>"/images/flags/IN.png",'feature_id'=>$new_max_fid,'country_name'=>$country_name,'lat'=>$lat,'lng'=>$lng,'name'=>$name,'fully_qualified_name'=>$name,'feature_class'=>$featureclass,'feature_code'=>$featurecode,'placetype'=>$placetype,"timezone" => "","google_map_url"=>"","yahoo_map_url"=>"");
For inserting in Solr it requires api service
In Road there is the requirement of lat lng series to fullfill the road length and structure. For this "OpenStreetMap" table is required because table road structure not fullfill the road requirements.
Table Name :OpenStreetMap
id ( bigint )
length (double precision )
name (character varying(255) )
location ( geometry )
countrycode (character varying(3) )
openstreetmapid ( bigint )
oneway ( boolean )
streettype ( character varying(255) )
isin ( character varying(255) )
partialsearchname( text )
textsearchname ( text )
textsearchvector ( tsvector )
partialsearchvector (tsvector)
gid ( bigint )
shape( geometry)
Indexes:
"openstreetmap_pkey" PRIMARY KEY, btree (id)
"openstreetmap_gid_key" UNIQUE CONSTRAINT, btree (gid)
"openstreetmapcountryindex" btree (countrycode)
"shapeindexopenstreetmap" gist (shape)
"streetosmgidindex" btree (gid)
"streetosmonewayindex" btree (oneway)
"streetosmopenstreetmapidindex" btree (openstreetmapid)
"streetosmtypeindex" btree (streettype)
//before push road ,first get max id and max feature_id from table openstreetmap
Example:
$result1 = pg_query($db_connection, "SELECT max(gid) as max_gid, max(id) as max_id FROM openstreetmap");
$row1=pg_fetch_object($result1);
$maxgid=$row1->max_gid;
$maxid=$row1->max_id;
- shape,location: Both of geometry type. Main difference of shape from location is shape store series of long and lat
Ex: $lng_lat_series="80.24960160255432 26.507440705757638,80.24931192398071 26.507167077436733,80.24919390678406 26.506960655640555, 80.24870038032532 26.506432598194902,80.24861991405487 26.506296382982715,80.24858236312866 26.505931541442614, 80.24843484163284 26.505456285593556,80.24816393852234 26.504457761849785,80.24800837039948 26.50403290656754, 80.24755775928497 26.503483232713055,80.24594843387604 26.501660769036295"; //comma sep
$shape_base='SRID=4326;LINESTRING('.$lng_lat_series.')';
Note: shape will be of any form : if road is rounded then start lng,lat equal to end lng,lat - street_type: This will be of many type : BYWAY,MINOR,SECONDARY_LINK,ROAD,PATH and many more. for see http://52.74.144.159:8080/public/streetSearch.html?advancedSearch=true
Example:
$query_string="insert into OpenStreetMap (countryCode, gid, isIn, length, location, name, oneWay, openstreetmapId, partialSearchName, shape, streetType, textSearchName, id) values ('$countryCode', $new_max_gid, '$isIn', '$length', '$location_base', '$location_name', '$one_way', NULL, NULL, '$shape_base', '$streetType', '$textSearchName', $new_max_id)";
- By Curl: use curl function and this function use webservice of gisgraphy .
Ex: - $url="http://52.74.144.159:8080/fulltext/fulltextsearch?q=Gooba+Garden+Road&placetype=street&country=IN&format=JSON&from=1&to=10";
- $url="http://52.74.144.159:8080/geoloc/geolocsearch?lat=$lat&lng=$lng&placetype=road&format=JSON&distance=true&from=1&to=1";
2.By Database:
- $query="SELECT name,astext(location) as lnglat,featurecode FROM road where featureid=$code ";
- $query="SELECT id, name, astext(location) as lnglat,featureid, CAST (st_distance_sphere(location, st_setsrid(st_makepoint($lng,$lat),4326)) AS INT) AS d FROM road ORDER BY location <-> st_setsrid(st_makepoint($lng,$lat), 4326) LIMIT 1";
When constructing a query it is important to remember that only the bounding-box-based operators such as && can take advantage of the GiST spatial index. Functions such as distance() cannot use the index to optimize their operation. For example, the following query would be quite slow on a large table:
SELECT the_geom
FROM geom_table
WHERE ST_Distance(the_geom, ST_GeomFromText('POINT(100000 200000)', -1)) < 100
This query is selecting all the geometries in geom_table which are within 100 units of the point (100000, 200000). It will be slow because it is calculating the distance between each point in the table and our specified point, ie. one ST_Distance() calculation for each row in the table. We can avoid this by using the && operator to reduce the number of distance calculations required:
SELECT the_geom
FROM geom_table
WHERE the_geom && 'BOX3D(90900 190900, 100100 200100)'::box3d
AND
ST_Distance(the_geom, ST_GeomFromText('POINT(100000 200000)', -1)) < 100
This query selects the same geometries, but it does it in a more efficient way. Assuming there is a GiST index on the_geom, the query planner will recognize that it can use the index to reduce the number of rows before calculating the result of the distance() function. Notice that the BOX3D geometry which is used in the && operation is a 200 unit square box centered on the original point - this is our "query box". The && operator uses the index to quickly reduce the result set down to only those geometries which have bounding boxes that overlap the "query box". Assuming that our query box is much smaller than the extents of the entire geometry table, this will drastically reduce the number of distance calculations that need to be done.
Following are the Step by step direction how to use function and classes(PHP and Java) to get and add location and its attributes.
In pgAdmin, select the required target scheme in object tree
Click on Plugins/PSQL Console
Write \i /path/to/yourfile.sql
Press enter