1 | package org.greenstone.server;
|
---|
2 |
|
---|
3 | import java.sql.Connection;
|
---|
4 | import java.sql.DriverManager;
|
---|
5 | import java.sql.ResultSet;
|
---|
6 | import java.sql.Statement;
|
---|
7 | import java.util.ArrayList;
|
---|
8 | import org.greenstone.client.Place;
|
---|
9 | import java.util.HashMap;
|
---|
10 |
|
---|
11 | public class PlaceInformation
|
---|
12 | {
|
---|
13 | static Statement _database = null;
|
---|
14 | static HashMap<String, ArrayList<Place>> _knownPlaces = new HashMap<String, ArrayList<Place>>();
|
---|
15 |
|
---|
16 | /**
|
---|
17 | * Default constructor Creates a connection to the database
|
---|
18 | */
|
---|
19 | public static void init()
|
---|
20 | {
|
---|
21 | try
|
---|
22 | {
|
---|
23 | System.out.print("Loading postgresql driver... ");
|
---|
24 | Class.forName("org.postgresql.Driver");
|
---|
25 | Connection c = DriverManager.getConnection("jdbc:postgresql://localhost/GS3MapDB", "sam", "password");//DriverManager.getConnection("jdbc:postgresql://localhost/test", "sjm84", "password");
|
---|
26 | System.out.println("Done!");
|
---|
27 | _database = c.createStatement();
|
---|
28 | }
|
---|
29 | catch (Exception ex)
|
---|
30 | {
|
---|
31 | ex.printStackTrace();
|
---|
32 | }
|
---|
33 | }
|
---|
34 |
|
---|
35 | /**
|
---|
36 | * Searches the database for a specific place by using the place name and
|
---|
37 | * the parent's place names
|
---|
38 | *
|
---|
39 | * @param id
|
---|
40 | * is the id of the place to search for in the database
|
---|
41 | * @return the list of places found (in case there is more than one match)
|
---|
42 | */
|
---|
43 | public static ArrayList<Place> getSpecificPlace(Long id)
|
---|
44 | {
|
---|
45 | try
|
---|
46 | {
|
---|
47 | ResultSet results = _database.executeQuery("SELECT * FROM places WHERE id = " + id);
|
---|
48 |
|
---|
49 | // Go through the results and create a list of places
|
---|
50 | ArrayList<Place> placeList = new ArrayList<Place>();
|
---|
51 |
|
---|
52 | while (results.next())
|
---|
53 | {
|
---|
54 | Place p = Place.createPlaceFromInformation(results.getLong("id"), results.getString("placename"), results.getString("subregion"), results.getString("region"), results.getString("country"), results.getString("placetype"), (Float) (results.getObject("longitude")), (Float) (results.getObject("latitude")), results.getLong("population"));
|
---|
55 |
|
---|
56 | if (p != null)
|
---|
57 | {
|
---|
58 | placeList.add(p);
|
---|
59 | }
|
---|
60 | }
|
---|
61 |
|
---|
62 | if (placeList.size() == 0)
|
---|
63 | {
|
---|
64 | placeList = null;
|
---|
65 | }
|
---|
66 |
|
---|
67 | results.close();
|
---|
68 |
|
---|
69 | // Return the list of places
|
---|
70 | return placeList;
|
---|
71 | }
|
---|
72 | catch (Exception ex)
|
---|
73 | {
|
---|
74 | ex.printStackTrace();
|
---|
75 | return null;
|
---|
76 | }
|
---|
77 | }
|
---|
78 |
|
---|
79 | /**
|
---|
80 | * Searches the database for a specific place by using the place name and
|
---|
81 | * the parent's place names
|
---|
82 | *
|
---|
83 | * @param placeName
|
---|
84 | * is the name of the place to find
|
---|
85 | * @param parentPlaceName
|
---|
86 | * is the name of the place's parents (seperated by ", ")
|
---|
87 | * @return the list of places found (in case there is more than one match)
|
---|
88 | */
|
---|
89 | public static ArrayList<Place> getSpecificPlace(String placeName, String parentPlaceName)
|
---|
90 | {
|
---|
91 | // Make sure the place name to find is not null
|
---|
92 | if (placeName == null)
|
---|
93 | {
|
---|
94 | return null;
|
---|
95 | }
|
---|
96 |
|
---|
97 | ResultSet results = null;
|
---|
98 |
|
---|
99 | String firstParent = null;
|
---|
100 | String secondParent = null;
|
---|
101 | String thirdParent = null;
|
---|
102 |
|
---|
103 | // Separate the parents
|
---|
104 | if (parentPlaceName != null)
|
---|
105 | {
|
---|
106 | String[] parents = parentPlaceName.split(", ");
|
---|
107 |
|
---|
108 | if (parents.length == 1)
|
---|
109 | {
|
---|
110 | firstParent = parents[0];
|
---|
111 | }
|
---|
112 | else if (parents.length == 2)
|
---|
113 | {
|
---|
114 | firstParent = parents[0];
|
---|
115 | secondParent = parents[1];
|
---|
116 | }
|
---|
117 | else
|
---|
118 | {
|
---|
119 | firstParent = parents[0];
|
---|
120 | secondParent = parents[1];
|
---|
121 | thirdParent = parents[2];
|
---|
122 | }
|
---|
123 | }
|
---|
124 |
|
---|
125 | try
|
---|
126 | {
|
---|
127 | // If the place does not have a parent
|
---|
128 | if (parentPlaceName == null)
|
---|
129 | {
|
---|
130 | results = _database.executeQuery("SELECT * FROM places WHERE placename = '" + placeName.replaceAll("'", "\\\\'") + "' " + "and subregion is null " + "and region is null " + "and country is null;");
|
---|
131 | }
|
---|
132 | // If the place has one parent
|
---|
133 | else if (secondParent == null)
|
---|
134 | {
|
---|
135 | results = _database.executeQuery("SELECT * FROM places WHERE placename = '" + placeName.replaceAll("'", "\\\\'") + "' " + "and subregion is null " + "and region is null " + "and country = '" + firstParent.replaceAll("'", "\\\\'") + "';");
|
---|
136 | }
|
---|
137 | // If the place has two parents
|
---|
138 | else if (thirdParent == null)
|
---|
139 | {
|
---|
140 | results = _database.executeQuery("SELECT * FROM places WHERE placename = '" + placeName.replaceAll("'", "\\\\'") + "' " + "and subregion is null " + "and region = '" + firstParent.replaceAll("'", "\\\\'") + "' " + "and country = '" + secondParent.replaceAll("'", "\\\\'") + "';");
|
---|
141 | }
|
---|
142 | // If the place has three parents
|
---|
143 | else
|
---|
144 | {
|
---|
145 | results = _database.executeQuery("SELECT * FROM places WHERE placename = '" + placeName.replaceAll("'", "\\\\'") + "' " + "and subregion = '" + firstParent.replaceAll("'", "\\\\'") + "' " + "and region = '" + secondParent.replaceAll("'", "\\\\'") + "' " + "and country = '" + thirdParent.replaceAll("'", "\\\\'") + "';");
|
---|
146 | }
|
---|
147 |
|
---|
148 | // Go through the results and create a list of places
|
---|
149 | ArrayList<Place> placeList = new ArrayList<Place>();
|
---|
150 |
|
---|
151 | while (results.next())
|
---|
152 | {
|
---|
153 | Place p = Place.createPlaceFromInformation(results.getLong("id"), results.getString("placename"), results.getString("subregion"), results.getString("region"), results.getString("country"), results.getString("placetype"), (Float) (results.getObject("longitude")), (Float) (results.getObject("latitude")), results.getLong("population"));
|
---|
154 |
|
---|
155 | if (p != null)
|
---|
156 | {
|
---|
157 | placeList.add(p);
|
---|
158 | }
|
---|
159 | }
|
---|
160 |
|
---|
161 | if (placeList.size() == 0)
|
---|
162 | {
|
---|
163 | placeList = null;
|
---|
164 | }
|
---|
165 |
|
---|
166 | results.close();
|
---|
167 |
|
---|
168 | // Return the list of places
|
---|
169 | return placeList;
|
---|
170 | }
|
---|
171 | catch (Exception ex)
|
---|
172 | {
|
---|
173 | ex.printStackTrace();
|
---|
174 | return null;
|
---|
175 | }
|
---|
176 | }
|
---|
177 |
|
---|
178 | /**
|
---|
179 | * Gets all of the place with the given name
|
---|
180 | *
|
---|
181 | * @param placeName
|
---|
182 | * @return
|
---|
183 | */
|
---|
184 | public static ArrayList<Place> getPlaces(String placeName)
|
---|
185 | {
|
---|
186 | ArrayList<Place> placeList = new ArrayList<Place>();
|
---|
187 |
|
---|
188 | ArrayList<Place> places = null;
|
---|
189 | if((places = _knownPlaces.get(placeName)) != null)
|
---|
190 | {
|
---|
191 | return places;
|
---|
192 | }
|
---|
193 |
|
---|
194 | try
|
---|
195 | {
|
---|
196 | if (_database == null)
|
---|
197 | {
|
---|
198 | System.out.println("***?***");
|
---|
199 | }
|
---|
200 | // Do a database query with the given place name
|
---|
201 | ResultSet results = _database.executeQuery("SELECT * FROM places WHERE placename = \'" + placeName.replaceAll("'", "\\\\'") + "\'");
|
---|
202 |
|
---|
203 | // Go through the results and add them to the list of found places
|
---|
204 | while (results.next())
|
---|
205 | {
|
---|
206 | Place p = Place.createPlaceFromInformation(results.getLong("id"), results.getString("placename"), results.getString("subregion"), results.getString("region"), results.getString("country"), results.getString("placetype"), (Float) (results.getObject("longitude")), (Float) (results.getObject("latitude")), results.getLong("population"));
|
---|
207 |
|
---|
208 | if (p != null)
|
---|
209 | {
|
---|
210 | placeList.add(p);
|
---|
211 | }
|
---|
212 | }
|
---|
213 | results.close();
|
---|
214 |
|
---|
215 | // Find if any places have this place name as a alternative name
|
---|
216 | ResultSet altNames = _database.executeQuery("SELECT * FROM alternatenames WHERE alternatename = \'" + placeName.replaceAll("'", "\\\\'") + "\'");
|
---|
217 |
|
---|
218 | ArrayList<Long> alternateIndexes = new ArrayList<Long>();
|
---|
219 |
|
---|
220 | while (altNames.next())
|
---|
221 | {
|
---|
222 | alternateIndexes.add(altNames.getLong("placeindex"));
|
---|
223 | }
|
---|
224 | altNames.close();
|
---|
225 |
|
---|
226 | for (Long index : alternateIndexes)
|
---|
227 | {
|
---|
228 | results = _database.executeQuery("SELECT * FROM places WHERE id = " + index);
|
---|
229 |
|
---|
230 | while (results.next())
|
---|
231 | {
|
---|
232 | Place p = Place.createPlaceFromInformation(results.getLong("id"), results.getString("placename"), results.getString("subregion"), results.getString("region"), results.getString("country"), results.getString("placetype"), (Float) (results.getObject("longitude")), (Float) (results.getObject("latitude")), results.getLong("population"));
|
---|
233 |
|
---|
234 | if (p != null)
|
---|
235 | {
|
---|
236 | placeList.add(p);
|
---|
237 | }
|
---|
238 | }
|
---|
239 | }
|
---|
240 | results.close();
|
---|
241 | }
|
---|
242 | catch (Exception ex)
|
---|
243 | {
|
---|
244 | ex.printStackTrace();
|
---|
245 | }
|
---|
246 |
|
---|
247 | _knownPlaces.put(placeName, placeList);
|
---|
248 |
|
---|
249 | return placeList;
|
---|
250 | }
|
---|
251 | } |
---|