Package org.localify.repositories

Interface CityRepository

All Superinterfaces:

org.springframework.data.repository.CrudRepository<City,UUID>, org.springframework.data.jpa.repository.JpaRepository<City,UUID>, org.springframework.data.repository.ListCrudRepository<City,UUID>, org.springframework.data.repository.ListPagingAndSortingRepository<City,UUID>, org.springframework.data.repository.PagingAndSortingRepository<City,UUID>, org.springframework.data.repository.query.QueryByExampleExecutor<City>, org.springframework.data.repository.Repository<City,UUID>

@Repository
public interface CityRepository
extends org.springframework.data.jpa.repository.JpaRepository<City,UUID>

Repository for City entities.

Method Summary

Modifier and Type	Method	Description
void	addCity(String name, String zoneCode, String countryCode, org.locationtech.jts.geom.Point geoPoint, String countryName)	Adds a new city.
List<City>	findAllByPlaylistNotNull()	Finds all cities that have a playlist.
List<City>	findAllNotGeocoded()	Finds all cities that have not been geocoded.
List<City>	findByCityAndCountry(String city, String country)	Finds cities by city name and country code, where the zone code is null.
List<City>	findByCityAndZoneAndCountryName(String city, String zoneCode, String countryName)	Finds cities by city name, zone code, and country name.
List<City>	findByCityAndZoneCodeWildcard(String name, String zoneCode, int limit)	Finds cities by name and zone code using a wildcard search.
List<City>	findByCityNameWildcard(String name, int limit)	Finds cities by name using a wildcard search.
List<City>	findByNameAndCountryCode(String name, String countryCode, int limit)	Finds cities by name and country code.
List<City>	findByNameAndZoneNameAndCountryCode(String name, String zoneName, String countryCode)	Finds cities by name, zone name, and country code.
List<City>	findByNameBreakTiesByDistance(String name, org.locationtech.jts.geom.Point point, int limit)	Finds cities by name, breaking ties by distance to a geographic point.
List<City>	findCitiesWithin(org.locationtech.jts.geom.Point point, Double radius)	Finds cities within a certain radius of a geographic point.
List<City>	findNearestCities(org.locationtech.jts.geom.Point point, int limit)	Finds the nearest cities to a geographic point.
City	findTopByCityAndZoneAndCountryCode(String city, String zone, String countryCode)	Finds a single city by city name, zone code, and country code.
List<City>	getCitiesWithoutTimezone(org.springframework.data.domain.Pageable pageable)	Gets a list of cities that do not have a timezone.
org.springframework.data.domain.Page<UUID>	getCityArtistProportions(org.springframework.data.domain.Pageable req)	Gets a page of city IDs, ordered by population per artist.
org.springframework.data.domain.Page<UUID>	getCityArtistProportionsByCreation(org.springframework.data.domain.Pageable req)	Gets a page of city IDs, ordered by the creation date of user-city relationships.
City	getCityFromId(UUID id)	Gets a city by its ID.
List<City>	getGoogleCitiesUpdatedOlderThan(Instant newest, org.springframework.data.domain.Pageable pageable)	Gets a list of cities that were updated from Google before a certain time.
List<City>	getPollstarFoundCitiesUpdatedOlderThan(Instant newest, org.springframework.data.domain.Pageable pageable)	Gets a list of cities found on Pollstar that were updated before a certain time.
List<City>	getPollstarNotFoundCities(Instant newest, org.springframework.data.domain.Pageable pageable)	Gets a list of cities not found on Pollstar that were queued for searching before a certain time.
List<City>	getPollstarNotSearchedCities(org.springframework.data.domain.Pageable pageable)	Gets a list of cities that have not been searched on Pollstar.

Methods inherited from interface org.springframework.data.repository.CrudRepository

count, delete, deleteAll, deleteAll, deleteAllById, deleteById, existsById, findById, save

Methods inherited from interface org.springframework.data.jpa.repository.JpaRepository

deleteAllByIdInBatch, deleteAllInBatch, deleteAllInBatch, deleteInBatch, findAll, findAll, flush, getById, getOne, getReferenceById, saveAllAndFlush, saveAndFlush

Methods inherited from interface org.springframework.data.repository.ListCrudRepository

findAll, findAllById, saveAll

Methods inherited from interface org.springframework.data.repository.ListPagingAndSortingRepository

findAll

Methods inherited from interface org.springframework.data.repository.PagingAndSortingRepository

findAll

Methods inherited from interface org.springframework.data.repository.query.QueryByExampleExecutor

count, exists, findAll, findBy, findOne

Method Details

findAllNotGeocoded

@Query("select c from City c where c.countryName is null")
List<City> findAllNotGeocoded()

Finds all cities that have not been geocoded.

Returns:

A list of cities.

findByNameBreakTiesByDistance

@Query(value="select * from cities where name ilike concat(:name, \'%\')\n             order by similarity(:name, name),\n             st_distance(geo_point, :point)\n             limit :limit\n",
       nativeQuery=true)
List<City> findByNameBreakTiesByDistance(String name,
 org.locationtech.jts.geom.Point point,
 int limit)

Finds cities by name, breaking ties by distance to a geographic point.

Parameters:

name - The name of the city.

point - The geographic point.

limit - The maximum number of results to return.

Returns:

A list of cities.

findByCityNameWildcard

@Query(value="select * from cities where unaccent(name) ilike concat(\'%\', :name, \'%\') and banned_at is null order by similarity(name, :name) desc, population desc nulls last limit :limit",
       nativeQuery=true)
List<City> findByCityNameWildcard(String name,
 int limit)

Finds cities by name using a wildcard search.

Parameters:

name - The name of the city.

limit - The maximum number of results to return.

Returns:

A list of cities.

findByCityAndZoneCodeWildcard

@Query(value="select * from cities where unaccent(name) ilike concat(\'%\', :name, \'%\') and zone_code ilike :zoneCode and banned_at is null order by (similarity(name, :name) + similarity(zone_code, :zoneCode)) desc, population desc nulls last limit :limit",
       nativeQuery=true)
List<City> findByCityAndZoneCodeWildcard(String name,
 String zoneCode,
 int limit)

Finds cities by name and zone code using a wildcard search.

Parameters:

name - The name of the city.

zoneCode - The zone code.

limit - The maximum number of results to return.

Returns:

A list of cities.

findTopByCityAndZoneAndCountryCode

@Query(value="select * from cities where unaccent(name) ilike unaccent(:city) and zone_code = :zone and country_code ilike :countryCode and banned_at is null limit 1",
       nativeQuery=true)
City findTopByCityAndZoneAndCountryCode(String city,
 String zone,
 String countryCode)

Finds a single city by city name, zone code, and country code.

Parameters:

city - The name of the city.

zone - The zone code.

countryCode - The country code.

Returns:

The city.

findByCityAndZoneAndCountryName

@Query(value="select * from cities where unaccent(name) ilike :city and zone_code = :zoneCode and country_name ilike :countryName and banned_at is null",
       nativeQuery=true)
List<City> findByCityAndZoneAndCountryName(String city,
 String zoneCode,
 String countryName)

Finds cities by city name, zone code, and country name.

Parameters:

city - The name of the city.

zoneCode - The zone code.

countryName - The name of the country.

Returns:

A list of cities.

findByCityAndCountry

@Query(value="select * from cities where unaccent(name) ilike unaccent(:city) and zone_code is null and country_code ilike :country and banned_at is null",
       nativeQuery=true)
List<City> findByCityAndCountry(String city,
 String country)

Finds cities by city name and country code, where the zone code is null.

Parameters:

city - The name of the city.

country - The country code.

Returns:

A list of cities.

findNearestCities

@Query(value="select *\nfrom cities\nwhere banned_at is null\norder by geo_point <-> :point\nlimit :limit\n",
       nativeQuery=true)
List<City> findNearestCities(org.locationtech.jts.geom.Point point,
 int limit)

Finds the nearest cities to a geographic point.

Parameters:

point - The geographic point.

limit - The maximum number of results to return.

Returns:

A list of cities.

getGoogleCitiesUpdatedOlderThan

@Query("        select c from City c\n        where (c.googleUpdatedAt is null or c.googleUpdatedAt <= :newest)\n        and (c.googleQueuedAt is null or c.googleQueuedAt <= :newest)\n")
List<City> getGoogleCitiesUpdatedOlderThan(Instant newest,
 org.springframework.data.domain.Pageable pageable)

Gets a list of cities that were updated from Google before a certain time.

Parameters:

newest - The timestamp to check against.

pageable - The pageable request.

Returns:

A list of cities.

findAllByPlaylistNotNull

List<City> findAllByPlaylistNotNull()

Finds all cities that have a playlist.

Returns:

A list of cities.

addCity

@Modifying
@Transactional
@Query(value="insert into cities (id, name, zone_code, country_code, geo_point, country_name, created_at, updated_at) values (gen_random_uuid(), :name, :zoneCode, :countryCode, :geoPoint, :countryName, current_timestamp, current_timestamp) on conflict do nothing",
       nativeQuery=true)
void addCity(String name,
 String zoneCode,
 String countryCode,
 org.locationtech.jts.geom.Point geoPoint,
 String countryName)

Adds a new city.

Parameters:

name - The name of the city.

zoneCode - The zone code.

countryCode - The country code.

geoPoint - The geographic point.

countryName - The name of the country.

findByNameAndCountryCode

@Query(value="select * from cities where name ilike concat(\'%\', :name, \'%\') and cities.country_code ilike :countryCode and banned_at is null order by (similarity(name, :name) + similarity(country_code, :countryCode)) desc limit :limit",
       nativeQuery=true)
List<City> findByNameAndCountryCode(String name,
 String countryCode,
 int limit)

Finds cities by name and country code.

Parameters:

name - The name of the city.

countryCode - The country code.

limit - The maximum number of results to return.

Returns:

A list of cities.

getCityArtistProportions

@Query(value="select city_id from\n(select c.id as city_id, c.name, c.zone_code, c.country_code,\n(case when count(a.id) = 0 then c.population else c.population / count(*) end) as popPerArtist\nfrom artist_cities ac\njoin cities c on ac.city_id = c.id\njoin artists a on ac.artist_id = a.id\nwhere c.population is not null\ngroup by c.id\norder by popPerArtist desc) as city_pop\n",
       nativeQuery=true)
org.springframework.data.domain.Page<UUID> getCityArtistProportions(org.springframework.data.domain.Pageable req)

Gets a page of city IDs, ordered by population per artist.

Parameters:

req - The pageable request.

Returns:

A page of city IDs.

getCityArtistProportionsByCreation

@Query(value="select c.id\nfrom cities c\njoin user_cities uc on c.id = uc.city_id\norder by uc.created_at desc\n",
       nativeQuery=true)
org.springframework.data.domain.Page<UUID> getCityArtistProportionsByCreation(org.springframework.data.domain.Pageable req)

Gets a page of city IDs, ordered by the creation date of user-city relationships.

Parameters:

req - The pageable request.

Returns:

A page of city IDs.

findByNameAndZoneNameAndCountryCode

@Query(value="select * from cities where unaccent(name) ilike unaccent(:name) and zone_name ilike :zoneName and country_code ilike :countryCode and banned_at is null",
       nativeQuery=true)
List<City> findByNameAndZoneNameAndCountryCode(String name,
 String zoneName,
 String countryCode)

Finds cities by name, zone name, and country code.

Parameters:

name - The name of the city.

zoneName - The name of the zone.

countryCode - The country code.

Returns:

A list of cities.

findCitiesWithin

@Query("select c from City c\nwhere st_distance(st_transform(c.geoPoint, 3857), st_transform(:point, 3857)) <= (:radius * 1000)\n")
List<City> findCitiesWithin(org.locationtech.jts.geom.Point point,
 Double radius)

Finds cities within a certain radius of a geographic point.

Parameters:

point - The geographic point.

radius - The radius in kilometers.

Returns:

A list of cities.

getPollstarNotFoundCities

@Query("select c from City c\n    where c.pollstarId is null\n    and c.pollstarQueuedAt is not null\n    and c.pollstarQueuedAt <= :newest\n")
List<City> getPollstarNotFoundCities(Instant newest,
 org.springframework.data.domain.Pageable pageable)

Gets a list of cities not found on Pollstar that were queued for searching before a certain time.

Parameters:

newest - The timestamp to check against.

pageable - The pageable request.

Returns:

A list of cities.

getPollstarNotSearchedCities

@Query("select c from City c\n    where c.pollstarId is null\n    and c.pollstarUpdatedAt is null\n    and c.pollstarQueuedAt is null\norder by c.population desc nulls last\n")
List<City> getPollstarNotSearchedCities(org.springframework.data.domain.Pageable pageable)

Gets a list of cities that have not been searched on Pollstar.

Parameters:

pageable - The pageable request.

Returns:

A list of cities.

getPollstarFoundCitiesUpdatedOlderThan

@Query("select c from City c\n    where c.pollstarId is not null\n    and c.pollstarUpdatedAt <= :newest\n    and c.pollstarQueuedAt <= :newest\n")
List<City> getPollstarFoundCitiesUpdatedOlderThan(Instant newest,
 org.springframework.data.domain.Pageable pageable)

Gets a list of cities found on Pollstar that were updated before a certain time.

Parameters:

newest - The timestamp to check against.

pageable - The pageable request.

Returns:

A list of cities.

getCitiesWithoutTimezone

@Query("select c from City c\nwhere c.timezone is null\norder by c.population desc nulls last\n")
List<City> getCitiesWithoutTimezone(org.springframework.data.domain.Pageable pageable)

Gets a list of cities that do not have a timezone.

Parameters:

pageable - The pageable request.

Returns:

A list of cities.

getCityFromId

@Query("select c from City c where c.id = :id")
City getCityFromId(UUID id)

Gets a city by its ID.

Parameters:

id - The ID of the city.

Returns:

The city.