
-- usage : psql -UYOURUSER -h 127.0.0.1 -d gisgraphy -f /path/to/file/createGISTIndex.sql


\connect gisgraphy
\echo will create all the index needed by gisgraphy to improve performance, this make take a while depends on how many data are in database
\echo will create Geonames Index
CREATE INDEX locationIndexAdm ON adm USING GIST (location);
CREATE INDEX locationIndexAirport ON airport USING GIST (location);
CREATE INDEX locationIndexAmusePark ON amusePark USING GIST (location);
CREATE INDEX locationIndexAqueduc ON quay USING GIST (location);
CREATE INDEX locationIndexATM ON ATM USING GIST (location);
CREATE INDEX locationIndexBank ON bank USING GIST (location);
CREATE INDEX locationIndexBar ON bar USING GIST (location);
CREATE INDEX locationIndexBay ON bay USING GIST (location);
CREATE INDEX locationIndexBeach ON beach USING GIST (location);
CREATE INDEX locationIndexBridge ON bridge USING GIST (location);
CREATE INDEX locationIndexBuilding ON building USING GIST (location);
CREATE INDEX locationIndexBusStation ON busStation USING GIST (location);
CREATE INDEX locationIndexCamp ON camp USING GIST (location);
CREATE INDEX locationIndexCanyon ON canyon USING GIST (location);
CREATE INDEX locationIndexCasino ON casino USING GIST (location);
CREATE INDEX locationIndexCastle ON castle USING GIST (location);
CREATE INDEX locationIndexCemetery ON cemetery USING GIST (location);
CREATE INDEX locationIndexCirque ON cirque USING GIST (location);
CREATE INDEX locationIndexCity ON city USING GIST (location);
CREATE INDEX locationIndexCitySubdivision ON citySubdivision USING GIST (location);
CREATE INDEX locationIndexCliff ON cliff USING GIST (location);
CREATE INDEX locationIndexCoast ON coast USING GIST (location);
CREATE INDEX locationIndexContinent ON continent USING GIST (location);
CREATE INDEX locationIndexCountry ON country USING GIST (location); 
CREATE INDEX locationIndexCourtHouse ON courtHouse USING GIST (location);
CREATE INDEX locationIndexCustomsPost ON customsPost USING GIST (location);
CREATE INDEX locationIndexDam ON dam USING GIST (location);
CREATE INDEX locationIndexDesert ON desert USING GIST (location);
CREATE INDEX locationIndexFactory ON factory USING GIST (location);
CREATE INDEX locationIndexFalls ON falls USING GIST (location);
CREATE INDEX locationIndexFarm ON farm USING GIST (location);
CREATE INDEX locationIndexField ON field USING GIST (location);
CREATE INDEX locationIndexFishingArea ON fishingArea USING GIST (location);
CREATE INDEX locationIndexFjord ON fjord USING GIST (location);
CREATE INDEX locationIndexForest ON forest USING GIST (location);
CREATE INDEX locationIndexGarden ON garden USING GIST (location);
CREATE INDEX locationIndexGisFeature ON gisFeature USING GIST (location);
CREATE INDEX locationIndexGolf ON golf USING GIST (location);
CREATE INDEX locationIndexGorge ON gorge USING GIST (location);
CREATE INDEX locationIndexGrassLand ON grassLand USING GIST (location);
CREATE INDEX locationIndexGulf ON gulf USING GIST (location);
CREATE INDEX locationIndexHill ON hill USING GIST (location);
CREATE INDEX locationIndexHospital ON hospital USING GIST (location);
CREATE INDEX locationIndexHotel ON hotel USING GIST (location);
CREATE INDEX locationIndexHouse ON house USING GIST (location);
CREATE INDEX locationIndexIce ON ice USING GIST (location);
CREATE INDEX locationIndexIsland ON island USING GIST (location);
CREATE INDEX locationIndexLake ON lake USING GIST (location);
CREATE INDEX locationIndexLibrary ON library USING GIST (location);
CREATE INDEX locationIndexLightHouse ON lightHouse USING GIST (location);
CREATE INDEX locationIndexMall ON mall USING GIST (location);
CREATE INDEX locationIndexMarsh ON marsh USING GIST (location);
CREATE INDEX locationIndexMetroStation ON metroStation USING GIST (location);
CREATE INDEX locationIndexMilitary ON military USING GIST (location);
CREATE INDEX locationIndexMill ON mill USING GIST (location);
CREATE INDEX locationIndexMine ON mine USING GIST (location);
CREATE INDEX locationIndexMole ON mole USING GIST (location);
CREATE INDEX locationIndexMonument ON monument USING GIST (location);
CREATE INDEX locationIndexMound ON mound USING GIST (location);
CREATE INDEX locationIndexMountain ON mountain USING GIST (location);
CREATE INDEX locationIndexMuseum ON museum USING GIST (location);
CREATE INDEX locationIndexOasis ON oasis USING GIST (location);
CREATE INDEX locationIndexObservatoryPoint ON observatoryPoint USING GIST (location);
CREATE INDEX locationIndexOcean ON ocean USING GIST (location);
CREATE INDEX locationIndexOperaHouse ON operaHouse USING GIST (location);
CREATE INDEX locationIndexPark ON park USING GIST (location);
CREATE INDEX locationIndexParking ON parking USING GIST (location);
CREATE INDEX locationIndexPlantation ON plantation USING GIST (location);
CREATE INDEX locationIndexPolicePost ON policePost USING GIST (location);
CREATE INDEX locationIndexPoliticalEntity ON politicalEntity USING GIST (location);
CREATE INDEX locationIndexPond ON pond USING GIST (location);
CREATE INDEX locationIndexPort ON port USING GIST (location);
CREATE INDEX locationIndexPostOffice ON postOffice USING GIST (location);
CREATE INDEX locationIndexPrison ON prison USING GIST (location);
CREATE INDEX locationIndexPyramid ON pyramid USING GIST (location);
CREATE INDEX locationIndexQuay ON quay USING GIST (location);
CREATE INDEX locationIndexRail ON rail USING GIST (location);
CREATE INDEX locationIndexRailRoadStation ON railRoadStation USING GIST (location);
CREATE INDEX locationIndexRanch ON ranch USING GIST (location);
CREATE INDEX locationIndexRavin ON ravin USING GIST (location);
CREATE INDEX locationIndexReef ON reef USING GIST (location);
CREATE INDEX locationIndexReligious ON religious USING GIST (location);
CREATE INDEX locationIndexReserve ON reserve USING GIST (location);
CREATE INDEX locationIndexRestaurant ON restaurant USING GIST (location);
CREATE INDEX locationIndexRoad ON road USING GIST (location);
CREATE INDEX locationIndexSchool ON school USING GIST (location);
CREATE INDEX locationIndexSea ON sea USING GIST (location);
CREATE INDEX locationIndexSpring ON spring USING GIST (location);
CREATE INDEX locationIndexStadium ON stadium USING GIST (location);
CREATE INDEX locationIndexStrait ON strait USING GIST (location);
CREATE INDEX locationIndexStream ON stream USING GIST (location);
CREATE INDEX locationIndexStreet ON street USING GIST (location);
CREATE INDEX locationIndexTheater ON theater USING GIST (location);
CREATE INDEX locationIndexTower ON tower USING GIST (location);
CREATE INDEX locationIndexTree ON tree USING GIST (location);
CREATE INDEX locationIndexTunnel ON tunnel USING GIST (location);
CREATE INDEX locationIndexUnderSea ON underSea USING GIST (location);
CREATE INDEX locationIndexVineyard ON vineyard USING GIST (location);
CREATE INDEX locationIndexVolcano ON volcano USING GIST (location);
CREATE INDEX locationIndexWaterBody ON waterBody USING GIST (location);
CREATE INDEX locationIndexZoo ON zoo USING GIST (location);

\echo will create Openstreetmap index

CREATE INDEX locationindexopenstreetmap ON openstreetmap USING GIST (location);
CREATE INDEX shapeindexopenstreetmap ON openstreetmap USING GIST (shape);
--CREATE INDEX textsearchvector_osm_idx ON openstreetmap  USING gin(textsearchvector);

VACUUM FULL ANALYZE;
