I have this query:
def floodhazard_tbl(request):
if request.method == "GET":
# create a list
to_json = []
# this code is results a messy JSON data that need underscore.js to manipulate
# in order for us to use datatables
getgeom1 = FloodHazard.objects.get(id=3).geom
response_high = list(PolyStructures.objects.filter(geom__within=getgeom1).values(
'brgy_locat', 'municipali').annotate(counthigh=Count('brgy_locat')))
to_json.append(response_high)
getgeom2 = FloodHazard.objects.get(id=2).geom
response_medium = list(PolyStructures.objects.filter(geom__within=getgeom2).values(
'brgy_locat', 'municipali').annotate(countmedium=Count('brgy_locat')))
to_json.append(response_medium)
getgeom3 = FloodHazard.objects.get(id=1).geom
response_low = list(PolyStructures.objects.filter(geom__within=getgeom3).values(
'brgy_locat', 'municipali').annotate(countlow=Count('brgy_locat')))
to_json.append(response_low)
return StreamingHttpResponse(list(json.dumps(to_json)), content_type='application/json')
Although it works, the respond is very slow and I wonder why.
Here's my model:
class PolyStructures(models.Model):
bldg_name = models.CharField(max_length=100)
block_name = models.CharField(max_length=75)
bldg_type = models.CharField(max_length=50)
brgy_locat = models.CharField(max_length=50)
municipali = models.CharField(max_length=50)
province = models.CharField(max_length=50)
geom = models.MultiPolygonField(srid=32651, null=True, blank=True)
objects = models.GeoManager()
def __unicode__(self):
return self.bldg_name
class FloodHazard(models.Model):
hazard = models.CharField(max_length=6)
date_field = models.DateTimeField(null=True, blank=True)
geom = models.MultiPolygonField(srid=32651, null=True, blank=True)
objects = models.GeoManager()
def __unicode__(self):
return self.hazard
Here's my JS function for displaying the result in my template:
function e() {
$.ajax({
url: "tbl/",
dataType: "json",
beforeSend: function () {
$("#info_tbl").show().text("Getting data from server...Please wait.").addClass("getme")
},
success: function (e) {
function t(e) {
return _.reduce(e, function (e, t) {
return e + parseFloat(t || 0)
}, 0)
}
$("#info_tbl").text("Successfully Loaded!").fadeOut(1e3).removeClass("getme"), $("#myErrorWrapper").hide(), $("#table_wrapper").show();
var a = _.chain(e).flatten().groupBy("brgy_locat", "municipali").map(function (e, r) {
return {
municipality: _(e).chain().pluck("municipali").first(),
brgy: r,
low: t(_(e).chain().pluck("countlow").value()),
medium: t(_(e).chain().pluck("countmedium").value()),
high: t(_(e).chain().pluck("counthigh").value())
}
}).value();
r.fnClearTable();
for (var o = 0; o < a.length; o++) r.fnAddData([a[o].municipality, a[o].brgy, a[o].low || 0, a[o].medium || 0, a[o].high || 0])
},
error: function () {
e(), $("#myErrorWrapper").show()
}
})
}
BTW, I'm using underscore.js and datatables plug-in.
The shapefile that I loaded in the PostgreSQL database has a size of 18mb and take up 3 rows in my table but before it was 28K+ rows, my co-worker "dissolved" it and make it in 3 (Low, Medium, High) rows.
I am using django-debug-toolbar to measure the query time.
Is there any way to make it fast? Or is it because of the shapefile size? or my query?