I have following given:
A database containing addresses with city, street, house number and an geometry.
@Id
@Column(name = "fid", unique = true)
private Integer fid;
@Column(name = "city")
private String city;
@Column(name = "street")
private String street;
@Column(name = "houseNumber")
private String houseNumber;
@javax.persistence.Column(name = "GEOM")
private Geometry geom;
I want the user to enter (a part of) street name and/or city name and search for matching addresses (SQL: "SELECT * FROM address WHERE street LIKE '%<streetinput>%' AND city LIKE '%<cityinput>%'").
In the end, a map will be zoomed to an envelope containing all matching addresses. Somewhat drill down on a map.
As the address table is rather large (small county: many 100000s of addresses), it is very inperformant to retrieve them all and handle the coordinates to determine the envelope.
Pseudocode:
envelope = emptyEnvelope;
foreach (address in foundAddresses) {
envelope.expandToInclude(address.geometry);
}
This could be feasable for some addresses found (upto hundreds) but not, if the user is has not yet drilled down to any of them (the whole database).
What I need now, is a way to tell hibernate spatial to return me the most minimum and the most maximum coordinates within the possible objects.
sql for oracle (only, in mysql, postgis, etc. the access will be different):
SELECT
MIN(b.GEOM.sdo_point.x) AS minX, MAX(b.GEOM.sdo_point.x) AS maxX,
MIN(b.GEOM.sdo_point.y) AS minY, MAX(b.GEOM.sdo_point.y) AS maxY
FROM
addresses b
WHERE street LIKE ... AND city LIKE ...;
This way an database index can be used an the envelope is retrieved instantly.
Is there a way to use hibernate spatial to query an envelope of a bunch of entries? How is it performed?
First of all Hibernate works on entities. Your final query does not fit on any entity it is a custom data. But you can search like this query and you can get your result. I think your final query is best for performance but if you want to use hibernate spatial queries think about accessing entities. You can do 4 different query and get the corner entities then manualy access the corner coordinates. You can build your query depending on this documentation.